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;
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;
PROC TRANSPOSE
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.
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;
Yes this worked. The first simple example I looked at was a bit too simple. Thank you very much!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.