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

I have the following data:

 

C-00118247                                        

Prom_Code     Mat_1     Mat_2     Mat_3      Mat_4    Mat_5      Mat_ 6    Mat_7   ......Mat_70

C-00118619    47375     47379     47377     47376     28763     27033     27034            
C-00118512    60205                                   
C-00118528    50001     50206     50006    
C-00118839                                        
C-00118865                                        
C-00118868   30796                                      
C-00118856                                        
C-00119741   38010      27033                                   
C-00119649                                        
C-00119448                                        
C-00119449                                        
C-00120397    347     744  

 

I am looking to get this output:

 

Promo_Code  Material

C-00118247    
C-00118619    47375
C-00118512    60205
C-00118528    50001
C-00118839   
C-00118865    
C-00118868    30796
C-00118856    
C-00119741    27033
C-00119649    
C-00119448  
C-00119449    
C-00120397    347  

C-00118247    
C-00118619    47379
C-00118512    
C-00118528    50206
C-00118839    
C-00118865    
C-00118868    
C-00118856    
C-00119741    27033
C-00119649    
C-00119448    
C-00119449    
C-00120397    744    

C-00118247    
C-00118619    47377
C-00118512    
C-00118528    50006
C-00118839    
C-00118865    
C-00118868    
C-00118856    
C-00119741   
C-00119649    
C-00119448    
C-00119449    
C-00120397     

etc.....     

 

I am currently using the following code, creating separate small files and appending them together.

When there was only 5 or so, it was not so bad, but there are now 70....any ideas on a simpler way to do this?

Thank you!

 

PROC SQL;
   CREATE TABLE WORK.MATERIAL_1 AS
   SELECT t1.PROMO_CODE,
          t1.MATERIAL_1 as t1.MATERIAL
      FROM WORK.QUERY_FOR_FIX_PLANNER_FILE t1;
QUIT;     

 

PROC SQL;
   CREATE TABLE WORK.MATERIAL_2 AS
   SELECT t1.PROMO_CODE,
          t1.MATERIAL_2 as t1.MATERIAL
      FROM WORK.QUERY_FOR_FIX_PLANNER_FILE t1;
QUIT;   

 

PROC SQL;
   CREATE TABLE WORK.MATERIAL_3 AS
   SELECT t1.PROMO_CODE,
          t1.MATERIAL_3 as t1.MATERIAL
      FROM WORK.QUERY_FOR_FIX_PLANNER_FILE t1;
QUIT;       

 

PROC SQL;
CREATE TABLE WORK.APPEND_MATERIALS AS
SELECT * FROM WORK.MATERIAL_1
 OUTER UNION CORR
SELECT * FROM WORK.MATERIAL_2
 OUTER UNION CORR
SELECT * FROM WORK.MATERIAL_C3;

QUIT;                                                                                                                    

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

That's not correct, you likely did something wrong....here's an example:

 

data have;
input code $ mat1 $ mat2 $ mat3 $ mat4 $ mat5 $;
cards;
A 454 KHKD 433 KHFD DRW
B dkf KFH KHF ljf kdf
C dkfj KHH dfk LIN INL
D akn KND NKD lNK lnf
;
run;

proc sort data=have; by code;
run;

proc transpose data=have out=want;
by code;
var mat1-mat5;
run;

View solution in original post

4 REPLIES 4
Reeza
Super User

PROC TRANSPOSE

cbrotz
Pyrite | Level 9

Unfortunately, transpose is not working.  For starters, this data is alpha and it looks like transpose only works on numeric.  Even when I change to numeric, transpose drops the prom_code which I need attached to each record as I have in my example.

Reeza
Super User

That's not correct, you likely did something wrong....here's an example:

 

data have;
input code $ mat1 $ mat2 $ mat3 $ mat4 $ mat5 $;
cards;
A 454 KHKD 433 KHFD DRW
B dkf KFH KHF ljf kdf
C dkfj KHH dfk LIN INL
D akn KND NKD lNK lnf
;
run;

proc sort data=have; by code;
run;

proc transpose data=have out=want;
by code;
var mat1-mat5;
run;
cbrotz
Pyrite | Level 9

Yes this worked.  The first simple example I looked at was a bit too simple.  Thank you very much!

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