topic how to merge multiple records into one single row based on ID field in New SAS User

how to merge multiple records into one single row based on ID field

student168 — Tue, 09 Oct 2018 22:39:27 GMT

I am trying to find the best way to do this? The objective is to get the output file. The Id field can have mult instances.

Input file (4 records)

AMT ID (PK)

00000100.00 341087625
00000200.00 341087625
00000500.00 123456789
00000500.00 123456789

00000500.00 123456789

ouput file(2 records)

ID_NUM AMT1 AMT2 AMT3

341087625 00000100.00 00000200.00 .

123456789 00000500.00 00000500.00 00000500.00

SAS

DATA WORK1;
INFILE INFILE1;
INPUT @01 Amount 11.2
@20 ID_NUM 9.

PROC SORT DATA=WORK1;
BY ID_NUM;

DATA WORK2;
SET WORK1;

{I really don't know how to do this Should I be using using a proc sql ?}

PROC SORT DATA=WORK2;
BY ID_NUM;

DATA _NULL_;
SET WORK3;
FILE OUTPUT1;

PUT @01 ID_NUM 9.
@10 Amt1 11.2

@50 Amt2 11.2

@100 Amt3 11.2

;

Re: how to merge multiple records into one single row based on ID field

ballardw — Tue, 09 Oct 2018 22:43:56 GMT

See if this gets you started:

proc transpose data=work1 out=want
   prefix=amt ;
by id_num;
var amt;
run;

This would go after your Proc sort.

What you are showing is transposing data, going from a long to wide structure. Proc Transpose does basic tasks for moving from wide to long or long to wide.

The presence of other variables and what happens with them would need to be indicated to provide additional help.

Re: how to merge multiple records into one single row based on ID field

student168 — Wed, 10 Oct 2018 03:59:25 GMT

So sent to an output file. Is there a way to remove _name_ field from displaying and only display just the 3 variables or just display just records? i don't want it really to create a report but not sure if proc print had the capability to just spit out report like my example?

The SAS System

IDNUM _Name_ customer1 customer2 customer3

123456789 INFO 0001 0004138 00000112.50 0001 0009289 00000054.36
341087625 INFO 0001 0004138 00000112.50 0001 0009289 00000054.36 0001 0009289 00000054.36

New output would just have the 2 records?

Example

123456789 INFO 0001 0004138 00000112.50 0001 0009289 00000054.36
341087625 INFO 0001 0004138 00000112.50 0001 0009289 00000054.36 0001 0009289 00000054.36

sas :

OPTIONS NODATE PAGENO=1 LINESIZE=100 PAGESIZE=40;

PROC TRANSPOSE DATA=WORK1 OUT=WORK2 PREFIX=CUSTOMER;
BY IDNUM;
VAR INFO;

PROC PRINTTO PRINT=OUTPUT1;
PROC PRINT DATA=WORK2 NOOBS;

Re: how to merge multiple records into one single row based on ID field

student168 — Wed, 10 Oct 2018 13:34:53 GMT

I found a way remove the titles;

link

https://communities.sas.com/t5/SAS-Programming/Exporting-Importing-CSV-file-in-SAS-9-4/m-p/503044

Re: how to merge multiple records into one single row based on ID field

ballardw — Wed, 10 Oct 2018 14:53:46 GMT

@student168 wrote:

So sent to an output file. Is there a way to remove _name_ field from displaying and only display just the 3 variables or just display just records? i don't want it really to create a report but not sure if proc print had the capability to just spit out report like my example?

You can drop, keep or rename variables with data set options most places a dataset is referenced to drop the _name_ variable

PROC TRANSPOSE DATA=WORK1 
          OUT=WORK2 (drop= _name_ )
           PREFIX=CUSTOMER
;
BY IDNUM;
VAR INFO;