I have data that is in multiple rows, which I need to create one row. Along with this each of the fields in the one row need to be built on data from the multiple rows.
What I have | |||
MONTH | VALUEa | VALUEb | VALUEc |
April | $100 | $43 | $143 |
May | $200 | $29 | $229 |
Jun | $300 | $20 | $320 |
What I need...
What I need | ||||||||
April VALUEa | April VAULEb | April VALUEc | May VALUEa | May VALUEb | May VALUE c | Jun VALUEa | Jun VALUEb | Jun VALUE c |
$100 | $43 | $143 | $200 | $29 | $229 | $300 | $20 | $320 |
Check this paper written by me,Matt,Arthur.T , which talk about this question.
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf
data have;
input MONTH $ (VALUEa VALUEb VALUEc) (: dollar12.) ;
format VALUEa VALUEb VALUEc dollar12.;
cards;
April $100 $43 $143
May $200 $29 $229
Jun $300 $20 $320
;
run;
proc sql;
select distinct catt('have(where=(month="',month,'") rename=(VALUEa=',month,'_VALUEa VALUEb=',month,'_VALUEb VALUEc=',month,'_VALUEc))') into : list separated by ' '
from have;
quit;
data want;
merge &list ;
drop month;
run;
Xia Keshan
I'm not going to argue with a working solution, but ...
In my experience, this transformation is unnecessary better than 90% of the time. In the vast majority of cases, you can get the results you want by using the data in its original form. You might have to learn additional programming techniques, but those techniques will come in handy throughout your programming career.
If you were to spell out your ultimate goal with the data, you would be pretty sure to get plenty of suggestions about how you can use the original data to get there.
Ultimately what I’m looking for is a way to accomplish this without a lot of hardcoding as this table is going to grow. Currently I have about 10 fields that I am using to do my grouping ~ each field will drill down to a deeper level. This will allow the end user to report from the highest to lowest level based on their needs. So these 10 fields will be what they are going to search on. Then with that I’ll have my month field (which eventually will have all 12 months, plus the year), and then the various value fields that I am doing summations on. The end report will break out the Value fields based on month, quarter and YTD.
So, for my example, I will have APR_Valuea, APR_Valueb, APR_Valuec, MAY_Valuea, MAY_Valueb, MAY_Valuec and so on for the month, quarter and YTD. For each month, there could be 5-6 fields created, same for Quarter and YTD. There will be a ton of fields, so I want to make this the least hard-coded way possible.
Make sense?
think what Astounding is trying to get at, and would also be my suggestion, is that the data structure you already have:
What I have | |||
MONTH | VALUEa | VALUEb | VALUEc |
April | $100 | $43 | $143 |
May | $200 | $29 | $229 |
Jun | $300 | $20 | $320 |
Is far easier to maintain and use. If you have 10 values, and 6 years, your going to end up with 6 * 12 * 10 variables. This quickly gets out of control. Then there is how are you going to process the data further down the line. You are going to need lists of variables etc. Ask yourself if there really is any benefit to changing the structure of the dataset.
Check this paper written by me,Matt,Arthur.T , which talk about this question.
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf
data have;
input MONTH $ (VALUEa VALUEb VALUEc) (: dollar12.) ;
format VALUEa VALUEb VALUEc dollar12.;
cards;
April $100 $43 $143
May $200 $29 $229
Jun $300 $20 $320
;
run;
proc sql;
select distinct catt('have(where=(month="',month,'") rename=(VALUEa=',month,'_VALUEa VALUEb=',month,'_VALUEb VALUEc=',month,'_VALUEc))') into : list separated by ' '
from have;
quit;
data want;
merge &list ;
drop month;
run;
Xia Keshan
This is EXACTLY what I needed, thank you!
Don't do it. But if you must then use a double PROC TRANSPOSE.
data have ;
input month $ value1-value3 ;
cards;
April 100 43 143
May 200 29 229
Jun 300 20 320
;;;;
proc transpose data=have out=middle ;
by month notsorted;
var value1-value3 ;
run;
proc transpose data=middle delim=_ out=want (drop=_:);
id month _name_;
var col1 ;
run;
proc print;
run;
Change it to this structure AFTER you've subset your data based on the filters. This is a reporting structure not a data storage structure.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.