CODEWITHSUNDEEP
sasenterprise-guide
sonarclick
sonarclick

Reputation: 73

Modifying SAS dataset using condition

I have a dataset in SAS Enterprise Guide that looks like this:

ID | Year | Entity | Inv1 | Inv2 | Inv3 | Inv4 | Inv5
1 | 2015 | A | 2 | 3 | . | 5 | .
1 | 2016 | A | . | 2 | 3 | . | .
2 | 2015 | B | 1 | . | 3 | . | 4
3 | 2016 | C | . | 2 | . | 3 | .
2 | 2016 | B | 5 | . | . | 2 |.
3 | 2015 | C | . |1 | . | .| 3

and I want to transform it into:

ID | Year | Entity | Inv | Value
1 | 2015 | A | Inv1 | 2
1 | 2015 | A | Inv2 | 3
1 | 2015 | A | Inv4 | 5
1 | 2016 | A | Inv2 | 2
1 | 2016 | A | Inv3 | 3
2 | 2015 | B | Inv1 | 1
2 | 2015 | B | Inv3 | 3
2 | 2015 | B | Inv5 | 4
3 | 2016 | C | Inv2 | 2
3 | 2016 | C | Inv4 | 3
2 | 2016 | B | Inv1 | 5
2 | 2016 | B | Inv4 | 2
3 | 2015 | C | Inv2 | 1
3 | 2015 | C | Inv5 | 3

I have searched on the internet and I have come up with this: http://support.sas.com/kb/24/635.html but my problem has extra dimensions and I'm stuck. Any help will be greatly appreciated!

Upvotes: 0

Views: 91

Answers (3)

Reeza
Reeza

Reputation: 21274

In EG, you can also use the Transpose Task.

Place the INV variables under the TRANSPOSE variables. Place ID YEAR ENTITY under the GROUP BY variables

You can customize your output dataset name under the Results options.

Upvotes: 0

Robert Soszyński
Robert Soszyński

Reputation: 1188

You have to create an array and using it, iterate between inv variables.

 data result;
    set source;

    array inv_tab(*) inv1-inv5;

    do i = 1 to dim(inv_tab);
        if inv_tab(i) ^= . then do;
            inv = vname(inv_tab(i));
            value = inv_tab(i);
            output;
        end;
    end;

    drop i inv1-inv5;
run;

Upvotes: 0

DomPazz
DomPazz

Reputation: 12465

This is a simple transpose.

First sort your data:

proc sort data=have;
by id year entity;
run;

Then transpose. You will rename columns and filter out the missing values:

proc transpose data=have out=want(where=(value1^=.)) name=Inv prefix=Value;
by id year entity;
var inv:;
run;

Proc Transpose will create the variable as Value1, you can rename that if you want.

proc datasets lib=work nolist;
modify want;
   rename Value1=Value;
run;
quit;

Upvotes: 1

Related Questions