DATA Step, Macro, Functions and more

creating column for each of value in variable

Reply
Contributor
Posts: 33

creating column for each of value in variable

[ Edited ]

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

PROC Star
Posts: 1,193

Re: creating column for each of value in variable

Posted in reply to Attyslogin

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

Contributor
Posts: 33

Re: creating column for each of value in variable

i guess i did not select RTF format while posting query. I have re-edited post. Hope now its clear enough to understand
Super User
Super User
Posts: 9,227

Re: creating column for each of value in variable

Posted in reply to Attyslogin

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.

Trusted Advisor
Posts: 1,294

Re: creating column for each of value in variable

Posted in reply to Attyslogin

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?

Super User
Posts: 9,611

Re: creating column for each of value in variable

Posted in reply to Attyslogin

Attyslogin wrote:
Kindly revert with tested code. Thanks

 



Kindly revert with example data in a data step. Thanks.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 6,543

Re: creating column for each of value in variable

Posted in reply to Attyslogin

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.

Ask a Question
Discussion stats
  • 6 replies
  • 106 views
  • 0 likes
  • 6 in conversation