topic Re: Create dynamic field names, creating one record from many in SAS Programming

Create dynamic field names, creating one record from many

LisaSAS — Thu, 11 Jun 2015 19:00:32 GMT

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

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

Astounding — Thu, 11 Jun 2015 21:14:42 GMT

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.

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

LisaSAS — Fri, 12 Jun 2015 14:10:28 GMT

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?

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

Ksharp — Fri, 12 Jun 2015 14:17:06 GMT

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

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

RW9 — Fri, 12 Jun 2015 14:56:17 GMT

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.

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

Tom — Fri, 12 Jun 2015 17:01:34 GMT

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;

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

Reeza — Fri, 12 Jun 2015 17:16:04 GMT

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.

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

LisaSAS — Mon, 29 Jun 2015 18:44:15 GMT

This is EXACTLY what I needed, thank you!