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

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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