topic Re: creating column for each of value in variable in SAS Programming

creating column for each of value in variable

Attyslogin — Tue, 16 Jan 2018 12:47:54 GMT

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

Re: creating column for each of value in variable

PeterClemmensen — Tue, 16 Jan 2018 12:43:00 GMT

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

Re: creating column for each of value in variable

Attyslogin — Tue, 16 Jan 2018 12:49:54 GMT

i guess i did not select RTF format while posting query. I have re-edited post. Hope now its clear enough to understand

Re: creating column for each of value in variable

RW9 — Tue, 16 Jan 2018 12:59:20 GMT

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.

Re: creating column for each of value in variable

mkeintz — Tue, 16 Jan 2018 13:08:33 GMT

@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?

Re: creating column for each of value in variable

Kurt_Bremser — Tue, 16 Jan 2018 13:15:31 GMT

@Attyslogin wrote:
Kindly revert with tested code. Thanks

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

Re: creating column for each of value in variable

Astounding — Tue, 16 Jan 2018 14:23:20 GMT

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.