topic Re: Moving Data from Horizontal to Vertical in SAS Programming

Moving Data from Horizontal to Vertical

neveragain — Sun, 20 Jun 2021 21:29:51 GMT

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

Re: Moving Data from Horizontal to Vertical

PaigeMiller — Sun, 20 Jun 2021 22:05:39 GMT

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;

Re: Moving Data from Horizontal to Vertical

neveragain — Sun, 20 Jun 2021 22:32:54 GMT

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?

I ran the code and troubleshotted some things but the table is pulling no
observations.

Thank you again.

Re: Moving Data from Horizontal to Vertical

mkeintz — Mon, 21 Jun 2021 18:52:30 GMT

@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;

Re: Moving Data from Horizontal to Vertical

PaigeMiller — Mon, 21 Jun 2021 10:33:11 GMT

Very nice, @mkeintz , although probably not feasible if the real problem has a1-a100 and b1-b100

Re: Moving Data from Horizontal to Vertical

PaigeMiller — Mon, 21 Jun 2021 10:38:19 GMT

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

Re: Moving Data from Horizontal to Vertical

Ksharp — Mon, 21 Jun 2021 12:17:30 GMT

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;

Re: Moving Data from Horizontal to Vertical

Ksharp — Mon, 21 Jun 2021 12:21:47 GMT

Why not feasible ? Could be

rename=(a_1-a_100 = out1-out100)

Re: Moving Data from Horizontal to Vertical

PaigeMiller — Tue, 22 Jun 2021 11:40:34 GMT

@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

Re: Moving Data from Horizontal to Vertical

Ksharp — Tue, 22 Jun 2021 11:49:56 GMT

Therefore, we can find many gem about SAS skill at this forum when we get together.

Re: Moving Data from Horizontal to Vertical

PaigeMiller — Tue, 22 Jun 2021 11:56:03 GMT

@Ksharp wrote:
Therefore, we can find many gem about SAS skill at this forum when we get together.

Of all the very smart things @Ksharp has ever said, this is my favorite.