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!
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.
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.