merge more than two columns

[yoyong555]

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.

[novinosrin]

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;

[yoyong555]

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.

[novinosrin]

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;

[yoyong555]

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