PROC Transpose missing values

[jbighitter]

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

[art297 · Accepted solution]

Sounds like you want to include an ID variable and possibly a prefix. e.g.:

proc transpose data=need prefix=ID_ out=want;
  by companyid ;
  var value;
  id DataID;
run;

Art, CEO, AnalystFinder.com

[jbighitter]

Thanks!  Works great.  I was hoping for a simple solution and your's surely was.

[mbakare]

I have experienced similar problem in the past. What you have to do  prior to transpose is to set the missing value to let us say 999 so that you dont have a blank space for missing data point. You can easily do this by  using array so that it would fill all missing values across all the variables with 999 if that is the code you prefer to use as missing. You can't use 999 if any of your real data value has 999. Then you can now transpose. After transposing, replace all 999 back to missing value.

Hope this helps.

Moshood