topic Re: Simplify Repetetive SQL Steps in SAS Programming

Simplify Repetetive SQL Steps

cbrotz — Fri, 03 Nov 2017 16:51:14 GMT

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;

Re: Simplify Repetetive SQL Steps

Reeza — Fri, 03 Nov 2017 16:52:17 GMT

PROC TRANSPOSE

Re: Simplify Repetetive SQL Steps

cbrotz — Fri, 03 Nov 2017 18:03:19 GMT

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.

Re: Simplify Repetetive SQL Steps

Reeza — Fri, 03 Nov 2017 18:10:30 GMT

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;

Re: Simplify Repetetive SQL Steps

cbrotz — Fri, 03 Nov 2017 18:24:24 GMT

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