Hi everyone.
My data looks something like this (all blanks are missing values):
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.
It looks like you want Column-Row order of Count. In another thread yesterday, you have received a solution using Proc Transpose and Proc Sort. Here is an array-based solution.
%let rows = 4;
%let cols = 3;
data want;
array k[&rows,&cols] _temporary_;
do i = 1 by 1 until(eof);
set have end = eof;
array c count1 - count&cols;
do j = 1 to dim(c);
k[i, j] = c[j];
end;
end;
do j = 1 to dim(c);
do i = 1 to dim1(k);
if k[i, j] then do;
Location = i;
count = k[i,j];
output;
end;
end;
end;
stop;
keep Location count;
run;
You sample output is (dis?)ordered in a manner, whose pattern I fail to catch. It makes much more sense to me to have it naturally sequenced by location:
data have ;
input location count1-count3 ;
cards ;
1 12 . 7
2 10 13 2
3 5 . .
4 8 12 15
;
run ;
data want (drop = count1-count3) ;
set have ;
array c count: ;
do over c ;
count = c ;
if N (count) then output ;
end ;
run ;
In which case, you get:
location count ----------------- 1 12 1 7 2 10 2 13 2 2 3 5 4 8 4 12 4 15
Kind regards
Paul D.
It looks like you want Column-Row order of Count. In another thread yesterday, you have received a solution using Proc Transpose and Proc Sort. Here is an array-based solution.
%let rows = 4;
%let cols = 3;
data want;
array k[&rows,&cols] _temporary_;
do i = 1 by 1 until(eof);
set have end = eof;
array c count1 - count&cols;
do j = 1 to dim(c);
k[i, j] = c[j];
end;
end;
do j = 1 to dim(c);
do i = 1 to dim1(k);
if k[i, j] then do;
Location = i;
count = k[i,j];
output;
end;
end;
end;
stop;
keep Location count;
run;
Hello,
data want;
set have(keep=location count1 rename=(count1=count))
have(keep=location count2 rename=(count2=count))
have(keep=location count3 rename=(count3=count));
where count ne .;
run;
If you have many variables.
data have ;
input location count1-count3 ;
cards ;
1 12 . 7
2 10 13 2
3 5 . .
4 8 12 15
;
run ;
data temp;
set have;
array x{*} count:;
do i=1 to dim(x);
if not missing(x{i}) then do; count=x{i};output;end;
end;
keep location i count;
run;
proc sort data=temp out=want;
by i;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.