Creating N columns in a CREATE TABLE

Reply
New Contributor
Posts: 2

Creating N columns in a CREATE TABLE

Hello,

There might be an easy solution, but I have been struggling with this for a while. Say you have data looking like this:

AccountStateTypeYearAmount
1NYA2100
2NJA150
2NYB175
3NJB150
3NYA270
3NYB275
4NJA1100
4NJB250
4NYB160
4NYA280
4NYB270

I wan the resulting table to look like this:

AccountStateA_1A_2B_1B_2
1NY010000
2NJ50000
2NY00750
3NJ00500
3NY070075
4NJ1000050
4NY0806070

I tried writing code looking like this, but it doesn't work. Any ideas?

macro mac;

  %do n = 1 %to 2;

  sum(case where Year = &n then A else 0 END) AS A_&n,

  sum(case where Year = &n then B else 0 END) AS B_&n,

  %end;
%mend;

proc sql;

select
  account,

  state,

  %mac

FROM table1;

quit;

Super User
Super User
Posts: 7,430

Re: Creating N columns in a CREATE TABLE

Hi,

What you are attempting to do is called transposing data.  For this purpose there is the SAS function transpose.  You would need something like (assuming sorted):

data want;

     attrib col_head format=$200.;

     col_head=strip(type)||"_"||strip(put(year,best.);

run;

proc transpose data=have out=want ;

     var amount;

     by account state;

     id col_head;

     idlabel col_head;

run;

Respected Advisor
Posts: 3,124

Re: Creating N columns in a CREATE TABLE

Agreed that this is a typical "Proc Transpose" job, it works out of box for your purpose. , "notsorted" option seems to be supported in my SAS version (9.3), so as "delimiter=" option.

data have;

     infile cards dlm='09'x;

     input Account :$     State:$    Type:$     Year Amount;

     cards;

1    NY   A    2    100

2    NJ   A    1    50

2    NY   B    1    75

3    NJ   B    1    50

3    NY   A    2    70

3    NY   B    2    75

4    NJ   A    1    100

4    NJ   B    2    50

4    NY   B    1    60

4    NY   A    2    80

4    NY   B    2    70

;

proc transpose data=have out=w1 (drop=_name_Smiley Happy delimiter=_;

     by account state notsorted;

     id type year;

run;

/*this step is optional if you to replace all these missings with 0*/

proc stdize data=w1 out=want missing=0 reponly;

run;

Good luck,

Haikuo

Regular Contributor
Posts: 180

Re: Creating N columns in a CREATE TABLE

Try this:

proc summary data=have noprint nway;
  class account state type year;
  var amount;
  output out=summary(drop= _type_ _freq_) sum=Amount;
run;

proc transpose data=summary out=want(drop=_name_);
  by account state;
  id type year;
  var amount;
run;

New Contributor
Posts: 2

Re: Creating N columns in a CREATE TABLE

Thanks, got it to work!

Ask a Question
Discussion stats
  • 4 replies
  • 212 views
  • 0 likes
  • 4 in conversation