Hi SAS Community - I'm relatively new to SAS and I don't have much experience moving data around within tables using a data step. I'm trying to go from the Beginning table to the Ending table as seen below. Essentially, variables A_ and B_ have the same EX_Date, Sales_Date, SerialNo, and Price. I want to move A_ and B_ under the headings 1, 2, and 3 so that the data is organized by 1, 2, and 3.
Is there a specific function within a data step that can help with this? Would Array work? Any help is greatly appreciated.
Beginning Table | |||||||||
Ex_Date | Sales_Date | SerialNo | Price | A_1 | A_2 | A_3 | B_1 | B_2 | B_3 |
20121001 | 20130119 | Serial 01 | 1250 | 15 | 79 | 10 | 352 | 259 | 288 |
20121001 | 20130119 | Serial 02 | 1250 | 34 | 82 | 1 | 496 | 475 | 239 |
20121001 | 20130119 | Serial 03 | 1250 | 48 | 33 | 15 | 106 | 232 | 377 |
Ending Table | |||||||||
Ex_Date | Sales_Date | SerialNo | Price | 1 | 2 | 3 | |||
20121001 | 20130119 | Serial 01 | 1250 | 15 | 79 | 10 | |||
20121001 | 20130119 | Serial 01 | 1250 | 352 | 259 | 288 | |||
20121001 | 20130119 | Serial 02 | 1250 | 34 | 82 | 1 | |||
20121001 | 20130119 | Serial 02 | 1250 | 496 | 475 | 239 | |||
20121001 | 20130119 | Serial 03 | 1250 | 48 | 33 | 15 | |||
20121001 | 20130119 | Serial 03 | 1250 | 106 | 232 | 377 |
UNTESTED CODE (If you want tested code, please provide sample data following these instructions)
data want;
set have;
array a a_1-a_3;
array b b_1-b_3;
array out out1-out3;
do i=1 to dim(a);
out(i) = a(i);
end;
output;
do i=1 to dim(b);
out(i) = b(i);
end;
output;
drop a_: b_:;
run;
@neveragain wrote:
Hi PaigeMiller - thank you for the quick reply and for the instructions on
posting data. I'm non-native English and I'm having trouble
understadning how to use the macro. Is there a section of the instructions that is to be focused on?
There is HELP embedded in the macro, which is printed out at the link I gave, and an example. Do you have questions about it?
I ran the code and troubleshotted some things but the table is pulling no observations.
Important information: you can't just say "pulling no observations" and then provide no additional details. Saying it doesn't work and providing no other details leaves us unable to help. When your have SAS code that isn't working, SHOW US THE LOG.
@PaigeMiller 's response provides the most common approach to this problem - it has the very useful DO loop.
Here is another approach, which doesn't use a DO loop. It uses 2 SET statements. This means that each incoming observation is accessed twice, and each access followed by an OUTPUT statement. The first time the B variables are not read in, and the A variables are renamed (not copied) to OUT1, OUT2, and OUT3. The second time the A variables are not read in, and the B variables are renamed.
data want;
set have (drop=b_: rename=(a_1=out1 a_2=out2 a_3=out3));
output;
set have (drop=a_: rename=(b_1=out1 b_2=out2 b_3=out3));
output;
run;
Edit note: with's @Ksharp 's suggestion, this can be made robust against growing variable counts, as in:
data want;
set have (drop=b_: rename=(a_1-a_100 = out1-out100));
output;
set have (drop=a_: rename=(b_1-b_100 = out1-out100));
output;
run;
Very nice, @mkeintz , although probably not feasible if the real problem has a1-a100 and b1-b100
@Ksharp wrote:
Why not feasible ? Could be
rename=(a_1-a_100 = out1-out100)
Interesting, the SAS documentation does not mention this as a possibility for the RENAME= option.
https://documentation.sas.com/doc/en/pgmmvacdc/9.4/ledsoptsref/p09ikb01zz9knnn16y401utyq4un.htm
However, for the RENAME statement in a DATA step, this is specifically mentioned:
https://documentation.sas.com/doc/en/pgmmvacdc/9.4/lestmtsref/n0x16kvqkxxdx5n1t04voifvo8wo.htm
data have;
infile cards expandtabs truncover;
input (Ex_Date Sales_Date SerialNo) (:$20.) Price A_1 A_2 A_3 B_1 B_2 B_3;
cards;
20121001 20130119 Serial01 1250 15 79 10 352 259 288
20121001 20130119 Serial02 1250 34 82 1 496 475 239
20121001 20130119 Serial03 1250 48 33 15 106 232 377
;
data want;
set have;
array x{*} a_1--b_3;
do i=1 to dim(x) by 3;
_1=x{i}; _2=x{i+1}; _3=x{i+2};output;
end;
drop a_1--b_3 i;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: