BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LisaSAS
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

Check this paper written by me,Matt,Arthur.T  , which talk about this question.

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

Code: Program


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

View solution in original post

7 REPLIES 7
Astounding
PROC Star

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.

LisaSAS
Obsidian | Level 7

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Ksharp
Super User

Check this paper written by me,Matt,Arthur.T  , which talk about this question.

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

Code: Program


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

LisaSAS
Obsidian | Level 7

This is EXACTLY what I needed, thank you! 

Tom
Super User Tom
Super User

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;

Reeza
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 816 views
  • 2 likes
  • 6 in conversation