BookmarkSubscribeRSS Feed
Attyslogin
Obsidian | Level 7

Lets say i have data in following manner,

 

Category Sub-category month value

X A jan 10

X B jan 20

X C jan 30

X A feb 40

X B feb 50

X C feb 60

 

i want an output to be created something like below. Going forward if month gets added in previous datset i.e. month automatically following output populate third column for that particular month. I have already explored related few links but couldnt implement. Kindly revert with tested code. Thanks

 

Category Sub-category Jan Feb

X A 10 40

X B 20 50

X C 30 60

6 REPLIES 6
PeterClemmensen
Tourmaline | Level 20

Can you provide some example of what your data structure looks like?

Attyslogin
Obsidian | Level 7
i guess i did not select RTF format while posting query. I have re-edited post. Hope now its clear enough to understand
RW9
Diamond | Level 26 RW9
Diamond | Level 26

What you are talking about is transposing.  Unless there is a good reason to do so, e.g. for a report, don't.  And even then consider quite carefully, what happens after december for instance.  Anyways a two step approach (note no code provided - post test data in the form of a datastep - I am not here to type in that data):

1) proc means or sql to roll all your data up into cat, sub, month, and sum(value)

2) proc transpose that data based on cat, sub, var=sum(value), id month.

mkeintz
PROC Star

@Attyslogin wrote:

Lets say i have data in following manner,

 

....

Kindly revert with tested code. Thanks

 


 

tested code (bold-underline-italics are mine)? How about providing some data on which to test the code?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Astounding
PROC Star

A standard approach:

 

proc sort data=have;

by category subcategory;

run;

proc transpose data=have;

by category subcategory;

id month;

var value;

run;

 

The warnings you received from others are valid.  This program might be suitable for reporting purposes.  But for other purposes, learn how to process the data in its current form.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 6 replies
  • 1214 views
  • 0 likes
  • 6 in conversation