BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
student168
Calcite | Level 5

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

 ;

      

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

4 REPLIES 4
ballardw
Super User

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
Calcite | Level 5

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;

ballardw
Super User

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1038 views
  • 1 like
  • 2 in conversation