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

Hello Everyone,

I have a dataset in the long format that I would like to transpose to a wide dataset. I would also like
the months to remain in sequential order. The dataset is attached. The long data set is in this format:
FRUIT STATUS DATEMON DATEYR MONYR CASECOUNT
APPLE CLOSED 3 2020 Mar-20 1
APPLE OPEN 6 2020 Jun-20 1
GRAPES CLOSED 10 2020 Oct-20 1
GRAPES OPEN 5 2020 May-20 2
ORANGE CLOSED 8 2020 Aug-20 1
ORANGE OPEN 8 2020 Jul-20 2
PEAR CLOSED 9 2020 Sep-20 1
PEAR OPEN 11 2019 Nov-19 1

 

I would like the data set to look like below with the casecounts corresponding to the month they were opened or closed. For example, in March 2020, apple had one casecount therefore march 2020 would have 1 for apple with a closed status and so on. 
FRUIT STATUS Nov-19 Mar-20 May-20 Jun-20 Jul-20 Aug-20 Sep-20 Oct-20
APPLE CLOSED               1
APPLE OPEN 
GRAPES CLOSED 
GRAPES OPEN 
ORANGE CLOSED 
ORANGE OPEN 
PEAR CLOSED 
PEAR OPEN 

Any assistance would be great. Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

One trick for changing the order of variables in a dataset is to use some statement like RETAIN that sets the order of the variables without actually forcing the data step compiler to determine the other attributes of the variables.

data want;
  retain id y1999 y2000 ;
  set have;
run;

So in your case you can use an SQL query to get the list of names in the order you want.

proc sql noprint ;
  select distinct translate(monyr,'_','-') into :varlist separated by ' '
  from have
  order by dateyr,datemon
;

Then you can transpose as normal 

proc transpose data=have out=want(drop=_name_);
  by fruit status ;
  id monyr ;
  var casecount;
run;

And run another step to change the order of the variables.

data want ;
  retain fruit status &varlist;
  set want;
run;

Results:

Obs    fruit     status    Nov_19    Mar_20    May_20    Jun_20    Aug_20    Jul_20    Sep_20    Oct_20

 1     APPLE     CLOSED       .         1         .         .         .         .         .         .
 2     APPLE     OPEN         .         .         .         1         .         .         .         .
 3     GRAPES    CLOSED       .         .         .         .         .         .         .         1
 4     GRAPES    OPEN         .         .         2         .         .         .         .         .
 5     ORANGE    CLOSED       .         .         .         .         1         .         .         .
 6     ORANGE    OPEN         .         .         .         .         .         2         .         .
 7     PEAR      CLOSED       .         .         .         .         .         .         1         .
 8     PEAR      OPEN         1         .         .         .         .         .         .         .

 

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

One trick for changing the order of variables in a dataset is to use some statement like RETAIN that sets the order of the variables without actually forcing the data step compiler to determine the other attributes of the variables.

data want;
  retain id y1999 y2000 ;
  set have;
run;

So in your case you can use an SQL query to get the list of names in the order you want.

proc sql noprint ;
  select distinct translate(monyr,'_','-') into :varlist separated by ' '
  from have
  order by dateyr,datemon
;

Then you can transpose as normal 

proc transpose data=have out=want(drop=_name_);
  by fruit status ;
  id monyr ;
  var casecount;
run;

And run another step to change the order of the variables.

data want ;
  retain fruit status &varlist;
  set want;
run;

Results:

Obs    fruit     status    Nov_19    Mar_20    May_20    Jun_20    Aug_20    Jul_20    Sep_20    Oct_20

 1     APPLE     CLOSED       .         1         .         .         .         .         .         .
 2     APPLE     OPEN         .         .         .         1         .         .         .         .
 3     GRAPES    CLOSED       .         .         .         .         .         .         .         1
 4     GRAPES    OPEN         .         .         2         .         .         .         .         .
 5     ORANGE    CLOSED       .         .         .         .         1         .         .         .
 6     ORANGE    OPEN         .         .         .         .         .         2         .         .
 7     PEAR      CLOSED       .         .         .         .         .         .         1         .
 8     PEAR      OPEN         1         .         .         .         .         .         .         .

 

luvscandy27
Quartz | Level 8

The code works great! Also, I appreciate you providing a thorough explanation.

 

Thank you!

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
  • 2 replies
  • 504 views
  • 0 likes
  • 2 in conversation