BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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?
2 REPLIES 2
data_null__
Jade | Level 19
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]
deleted_user
Not applicable
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

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
  • 2 replies
  • 610 views
  • 0 likes
  • 2 in conversation