CODEWITHSUNDEEP
sqlsas
fmng
fmng

Reputation: 33

Columns valorization in SAS

this is my situation

1st table:

id
012
345
678

2nd table:

id flag
012 A
345 B
678 C

I need to use the ids as a key in order to obtain something like that:

id A B C
012 1 0 0
345 0 1 0
678 0 0 1

Upvotes: 0

Views: 163

Answers (2)

data _null_
data _null_

Reputation: 9109

SAS know a lot about dummy variables.

data have;
   infile cards expandtabs;
   input id:$3. flag:$1.;
   cards;
012 A
345 B
678 C
;;;;
proc print;
proc transreg data=have design;
   model class(flag / zero=none);
   output out=coded;
   run;
proc print;
   run;

Upvotes: 2

Reeza
Reeza

Reputation: 21274

From your example there's no requirement for Table 1 to be used that I can see. If there is a need, you'll need to modify your example to show how it's relevant.

Otherwise, a quick and dirty way:

  • Use PROC FREQ to generate the counts and set them as 1 and 0s
  • Transpose to desired data structure
  • If you just need displayed output, the PROC FREQ step is enough
proc freq data=have ;
table id*flag / sparse out=long nopercent norow nocol;
run;


proc transpose data=long out=want (drop = _:);
by id;
id flag;
var count;
run;

Upvotes: 1

Related Questions