BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jbighitter
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

 

View solution in original post

3 REPLIES 3
art297
Opal | Level 21

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
Fluorite | Level 6

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

mbakare
Calcite | Level 5

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 11753 views
  • 0 likes
  • 3 in conversation