How to use array to reshape data

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 130
Accepted Solution

How to use array to reshape data

[ Edited ]

In the following sample data,  there are two 1990-1 and 1991-3 (with different "values"). the PROC TRANSPOSE with LET option only keep the last one . How to use ARRAY to get the similar result as PROC TRANSPOSE but keep all observations? Thanks a lot!

 

data have;
input year month store $ value;
datalines;
1990 1 aa 80
1990 1 aa 100
1990 2 aa 85
1990 3 aa 90
1990 1 bb 97
1990 2 bb 101
1991 1 aa 87
1991 3 aa 88
1991 1 bb 92
1991 2 bb 91
1991 3 bb 89
1991 3 bb 87
;
run;

proc transpose data=have out=want let;
by year month;
var value;
id store;
quit;

The desired output is like:

2016-01-13_110344.jpg


Accepted Solutions
Solution
‎01-13-2016 12:26 PM
Esteemed Advisor
Esteemed Advisor
Posts: 7,199

Re: How to use array to reshape data

I am not seeing what the problem is then, just one if statement gets you your required output:

data want (drop=store);
  set have;
  if store="aa" then aa=value;
  else bb=value;
run;

View solution in original post


All Replies
Grand Advisor
Posts: 17,313

Re: How to use array to reshape data

What do you want the output to look like?

Contributor ndp
Contributor
Posts: 61

Re: How to use array to reshape data

You can add a counter (cnt) and then use it in proc transpose:

data have;
input year month store $  value ;
datalines;
1990 1 aa 80
1990 1 aa 100
1990 2 aa 85
1990 3 aa 90
1991 1 bb 92
1991 2 bb 91
1991 3 bb 89
1991 3 bb 87
;
run;

data have1;
	set have;
	by year month store;
	retain cnt;
	if first.store then cnt=0;
	cnt=cnt+1;
run;

proc transpose data=have1 out=want ;
by year month cnt;
var value;
id store;
quit;

 

Frequent Contributor
Posts: 130

Re: How to use array to reshape data

[ Edited ]

This works great for the sample data.

When I applied to the real dataset, the output format is what I want, but the table have many missing values (eg., the whole bb column is missing value). 

Frequent Contributor
Posts: 128

Re: How to use array to reshape data

I would reiterate what @ndp said, but I would apply your count variable on the year and month variables instead of the store.  Below should get you what you're looking for:

 

data have;
input year month store $ value;
datalines;
1990 1 aa 80
1990 1 aa 100
1990 2 aa 85
1990 3 aa 90
1991 1 bb 92
1991 2 bb 91
1991 3 bb 89
1991 3 bb 87
;
run;

data have_count;
set have;
by year month notsorted;
retain count;
count=count+1;
If First.Year AND First.Month then count=1;
run;

proc transpose data=have_count out=want (drop=count);
by year month count;
var value;
id store;
run;
Esteemed Advisor
Esteemed Advisor
Posts: 7,199

Re: How to use array to reshape data

I agree with Reeza, what do you want the output to look like.  I would add that it is not recommended to put data as column headers, this is a good example of why.  Column names should be something generic, and the label of the variable can be anything you want.  So in your example, it would matter if there are multiple of the same data element as you would have:

VAR1 (1990 1) VAR2 (1990 2) VAR3 ...

So column names are for Programming, column labels are for output.

Frequent Contributor
Posts: 130

Re: How to use array to reshape data

[ Edited ]

I updated the original post with the output format.

Solution
‎01-13-2016 12:26 PM
Esteemed Advisor
Esteemed Advisor
Posts: 7,199

Re: How to use array to reshape data

I am not seeing what the problem is then, just one if statement gets you your required output:

data want (drop=store);
  set have;
  if store="aa" then aa=value;
  else bb=value;
run;
Grand Advisor
Posts: 9,571

Re: How to use array to reshape data

Check MERGE skill I ,Matt and Arthur.T proposed :

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

 

data have;
input year month store $  value ;
datalines;
1990 1 aa 80
1990 1 aa 100
1990 2 aa 85
1990 3 aa 90
1991 1 bb 92
1991 2 bb 91
1991 3 bb 89
1991 3 bb 87
;
run;
proc sql;
select distinct catt('have(where=(year=',year,' and month=',month,' and store="',store,'") rename=(value=',store,'))') into : merge separated by ' '
 from have;
quit;
data want;
 merge &merge;
 by year month store;
 drop store;
run;
Frequent Contributor
Posts: 130

Re: How to use array to reshape data

Thank you for your paper!

Frequent Contributor
Posts: 130

Re: How to use array to reshape data

Thank you all for your great ideas! All approaches work great for the small sample. 

Originally I also thought the same way as RW9. and I found that there are quite some "store" values, therefore need to write many "if" statements. Then I hope that there is an easier approach (maybe I was kind of "want to much" Smiley Very Happy ). I think I will just go for the most simply way (which is easy for me to handle) and take extra steps to get the desired output.

Thank you all again for your kindly help!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 501 views
  • 4 likes
  • 6 in conversation