CODEWITHSUNDEEP
sas
LdM
LdM

Reputation: 704

how to manipulate a table using proc transpose

I have this table:

ID   100  105 201 305 502 
100   .   0.2 0.3 0.2 0.2
200   0.1  0  0.4 0.5 0.1
201   0.2 0.1 0.4 0.3 0.1
304   .   .   0.2 0.2 0.3

I would like to have something like this:

Code ID   Val
100  100  .
100  200  0.1
100  201  0.2
100  304  .
105  100  0.2
105  200  0
105  201  0.1
105  304  .
...

I have tried as follows

proc transpose data=my_data out=test(rename=col1=Val);

run;

But the output is different from what I would expect. I think I am using proc transpose in the wrong way. I would like to have Val as column not as a row.

Can you give me suggestions on how to get the expected output?

Upvotes: 0

Views: 196

Answers (1)

Kermit
Kermit

Reputation: 3117

I don't know what you are trying to do with numeric values as column names but it can be achieved in one proc transpose. The following code output the desired result:

data have;
infile datalines4 delimiter=",";
input ID "100"n "105"n "201"n "305"n "502"n ;
datalines4;
100,.,0.2,0.3,0.2,0.2
200,0.1,0,0.4,0.5,0.1
201,0.2,0.1,0.4,0.3,0.1
304,.,.,0.2,0.2,0.3
;;;;

proc transpose data=have out=stage1(rename=(_name_=Code col1=Val));
by id;
var "100"n "105"n "201"n "305"n "502"n;
run;

proc sort data=stage1 out=stage2;
by code id;
run;

data want;
retain code id val;
set stage2;
run;

Results (want table):

enter image description here

[Added based on @LdM comment]:

Just use the dictionary.columns table with proc sql to retrieve the variables that you want:

proc sql noprint;
select cats('"',name,'"','n') into :varnames separated by " "
from dictionary.columns
    where libname="WORK" and memname="HAVE" and name ne "ID";
quit;

proc transpose data=have out=stage1(rename=(_name_=Code col1=Val));
by id;
var &varnames.;
run;

Upvotes: 2

Related Questions