I'm attempting to transpose data to create a single instance for each primary key. The problem is some of the data items are missing so when I transpose the data the missing values are filled with the next observation.
Here's a subset of the data (it's in text format). The first CompanyID has no missing codes, the second Company ID has codes missing (e.g., data for code 14)
CompanyID | DataID | Value
0C000006SR|2|AXM Pharma
0C000006SR|3|AXM Pharma Inc
0C000006SR|4|AXM Pharma Inc
0C000006SR|5|USA
0C000006SR|6|1113643
0C000006SR|9|U
0C000006SR|10|12
0C000006SR|11|N
0C000006SR|12|0P0000003F
0C000006SR|13|AXMP
0C000006SR|14|PINX
0C000006SR|15|USA
0C000006SR|16|ENG
0C000006SR|17|Lopez, Blevins, Bork & Associates
0C000006SR|18|ENG
0C000006SR|19|Lopez, Blevins, Bork & Associates
0C000006SR|20|ENG
0C000006SR|21|0
0C000006SR|22|0
0C000006SR|23|PINX
0C000006SR|24|1
0C000006SR|25|1999
0C000006SR|26|0
0C000006YV|2|Bodisen Biotech
0C000006YV|3|Bodisen Biotech Inc
0C000006YV|4|Bodisen Biotech Inc
0C000006YV|5|USA
0C000006YV|6|1178552
0C000006YV|9|U
0C000006YV|10|12
0C000006YV|11|N
0C000006YV|12|0P000000TS
0C000006YV|13|BBCZ
0C000006YV|15|CHN
0C000006YV|16|ENG
0C000006YV|17|Clement C. W. Chan & Co
0C000006YV|18|ENG
0C000006YV|19|Clement C. W. Chan & Co
0C000006YV|20|ENG
0C000006YV|21|0
0C000006YV|22|0
0C000006YV|24|1
0C000006YV|25|2000
0C000006YV|26|0
0C000006YV|27|2013-12-31
The 2nd set of data is missing DataID 14 so it moves data item 15 into that location.
Here's my code:
proc sort data=sashelp out=need; by companyid DataID;
proc transpose data=need out=want;
by companyid ;
var value;
proc print; run;
The output is attached.
Thanks