topic Re: merge more than two columns in SAS Programming

merge more than two columns

yoyong555 — Fri, 22 Nov 2019 00:27:13 GMT

Hi everyone.

My data looks something like this:

Location Count1 Count2 Count3

1 12 7

2 10 13 2

3 5

4 8 12 15

And I want the data to look like this:

Location Count

1 12

2 10

3 5

4 8

2 13

4 12

1 7

2 2

4 15

Thanks for your help.

Re: merge more than two columns

novinosrin — Fri, 22 Nov 2019 00:33:09 GMT

HI @yoyong555

data have;
input Location            Count1      Count2           Count3;
cards;
1                        12                  .                    7
2                        10                  13                2
3                          5                 .              .
4                          8                  12                15
;

proc transpose data=have out=temp(where=(col1 ne .));
by location;
run;

proc sort data =temp out=want(drop=_name_);
by _name_;
run;

Re: merge more than two columns

yoyong555 — Fri, 22 Nov 2019 00:56:33 GMT

Thank you @novinosrin.

Do I need to indicate a . for all the blanks for this to work? I should have only 2 variables but the result had more than 2.

If I have 100 variables (Count1 - Count100), how do I assign a . to all the blanks? Thanks.

Re: merge more than two columns

novinosrin — Fri, 22 Nov 2019 01:06:07 GMT

Hi @yoyong555 I think you are refering to DATA HAVE step. That was just to create a sample and yes you would have to indicate with a . for missing values especially when you are trying to read raw data using LIST input . But as long as your sample is a representative one of your real, do not be concerned about that.

You just need to focus on

proc transpose data=have out=temp(where=(col1 ne .));
by location;
var count:;
run;

proc sort data =temp out=want(drop=_name_);
by _name_;
run;

Re: merge more than two columns

yoyong555 — Fri, 22 Nov 2019 02:05:52 GMT

Hi @novinosrin.
Here is the output.

Obs Location _NAME_ COL1
1 1 Count1 12
2 1 Count2 7
3 1 Count3 2
4 3 Count1 5
5 3 Count2 4
6 3 Count3 8