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

[student168]

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

 ;

[ballardw · Accepted solution]

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.

[student168]

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;

[student168]

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

[ballardw]

---

@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;