DATA Step, Macro, Functions and more

Simplify Repetetive SQL Steps

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

Simplify Repetetive SQL Steps

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;                                                                                                                    


Accepted Solutions
Solution
‎11-03-2017 02:22 PM
Super User
Posts: 23,296

Re: Simplify Repetetive SQL Steps

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


All Replies
Super User
Posts: 23,296

Re: Simplify Repetetive SQL Steps

PROC TRANSPOSE

Contributor
Posts: 52

Re: Simplify Repetetive SQL Steps

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.

Solution
‎11-03-2017 02:22 PM
Super User
Posts: 23,296

Re: Simplify Repetetive SQL Steps

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;
Contributor
Posts: 52

Re: Simplify Repetetive SQL Steps

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 105 views
  • 0 likes
  • 2 in conversation