topic Changing a SAS data value to a Var name in SAS Programming

Changing a SAS data value to a Var name

deleted_user — Tue, 10 Feb 2009 01:08:14 GMT

I have a dataset that repeats records. There are 2 values in the dataset:

FNAME FVAL

MI004 100002456
AS000
PAY201 234.00
MI004 10003444
AS000 APR 5.5
AS010 Conventional
MI004 100008976
XI900 X
XI920 YES
AS000 APR 1.1

MI004 is the key account number. I need the data to look like this:
SAS VAR Name
MI004 AS000 PAY201 AS0101 XI900 XI920

All associated data stored in the corrrect buckets by variable name.

Can you help?

Re: Changing a SAS data value to a Var name

data_null__ — Tue, 10 Feb 2009 11:55:49 GMT

Proc transpose is the tool you need. But before you can do that you will need to create a variable to group the data by account number. This program may be helpful.

[pre]
data tall;
input FNAME:$5. FVAL &$20.;
cards;
MI004 100002456
AS000
PAY201 234.00
MI004 10003444
AS000 APR 5.5
AS010 Conventional
MI004 100008976
XI900 X
XI920 YES
AS000 APR 1.1
;;;;
run;
data tallV/view=tallV;
set tall;
if fname eq: 'MI' then key = fval;
retain key;
run;
proc transpose data=tallV out=wide;
by key notsorted;
var fval;
id fname;
run;
proc print;
run;
[/pre]

Re: Changing a SAS data value to a Var name

deleted_user — Tue, 10 Feb 2009 11:57:59 GMT

looks like name-value pairs.
Is the list of names constant?
The program would need to recognise the end of data for one account.
Does each account start with it's account number?
Assuming "yes" answers:[pre] data loaded( keep= MI004 AS000 PAY201 AS0101 XI900 XI920 ) ;
length MI004 $10 as000 $8 pay201 8 as0101 $11 xi900 xi920
FNAME $8 ;
retain MI004 ;
infile datalines col=col eof= eod truncover ;
do until( fname= 'MI004' ) ;
input fname @ ;
select (fname) ;
when ( 'AS000' ) input as000 &;
when ( 'PAY201') input pay201 &;
when ( 'AS0101') input as0101 &;
when ( 'XI900' ) input xi900 &;
when ( 'XI920' ) input xi920 &;
when ( 'MI004' ) ; * save it for later ;
otherwise input ; * ignoring line ;
end ; * of select group ;
end ;
if not missing( mi004 ) then output ;
if fname= 'MI004' then input mi004 ; * unless at EOF ;
return ;
eod:
output ;
datalines ;
MI004 100002456
AS000
PAY201 234.00
MI004 10003444
AS000 APR 5.5
AS010 Conventional
MI004 100008976
XI900 X
XI920 YES
AS000 APR 1.1
;[/pre]That worked when I tested on win-XP in SAS9.1.3

good luck

PeterC