Create dynamic field names, creating one record from many

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

Create dynamic field names, creating one record from many

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


Accepted Solutions
Solution
‎06-12-2015 10:17 AM
Super User
Posts: 9,687

Re: Create dynamic field names, creating one record from many

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


All Replies
Super User
Posts: 5,085

Re: Create dynamic field names, creating one record from many

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.

Contributor
Posts: 31

Re: Create dynamic field names, creating one record from many

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?

Super User
Super User
Posts: 7,413

Re: Create dynamic field names, creating one record from many

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.

Solution
‎06-12-2015 10:17 AM
Super User
Posts: 9,687

Re: Create dynamic field names, creating one record from many

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

Contributor
Posts: 31

Re: Create dynamic field names, creating one record from many

This is EXACTLY what I needed, thank you! 

Super User
Super User
Posts: 6,502

Re: Create dynamic field names, creating one record from many

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=_Smiley Happy;

  id month _name_;

  var col1 ;

run;

proc print;

run;

Super User
Posts: 17,869

Re: Create dynamic field names, creating one record from many

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.

☑ This topic is SOLVED.

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

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