🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

## Need help with coding

Can someone help me convert TABLE 1 below to TABLE 2 by streamlining values (using SAS windows 9.4):

data temp;
infile datalines ;
input @1 ID \$3. @5 NET1 \$3. @9 NET2 \$3. @13 NET3 \$3.;
datalines;
HMO POS PPO
POS PPO
PPO
;
proc print data=temp;
run;

/*

Table 1

NET1 NET2 NET3
ID1  HMO  POS   PPO
ID2  POS   PPO
ID3  PPO

Table 2

NET1 NET2 NET3
ID1          HMO POS    PPO
ID2                   POS    PPO
ID3                              PPO

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Need help with coding

``````data temp;
infile datalines truncover;
input  ID :\$3. (net1-net3) (:\$3.);
datalines;
ID1  HMO  POS   PPO
ID2  POS   PPO
ID3  PPO
;
data want;
set temp;
array x{*} \$ net1-net3 ;
array y{*} \$ 80 _net1-_net3 ;
n=dim(x)+1;
do i=dim(x) to 1 by -1;
if not missing(x{i}) then do;n+(-1);y{n}=x{i};end;
end;
drop i n net1-net3;
run;
``````
10 REPLIES 10
Tourmaline | Level 20

## Re: Need help with coding

``````

data temp;
infile datalines truncover;
input  ID :\$3. (net1-net3) (:\$3.);
datalines;
ID1  HMO  POS   PPO
ID2  POS   PPO
ID3  PPO
;

data want;
set temp;
call sortc(of net:);
run;

proc print noobs;run;``````
ID net1 net2 net3
ID1 HMO POS PPO
ID2   POS PPO
ID3     PPO
Super User

## Re: Need help with coding

So you decided the question was how to sort the values in an array (set of variables).

Tourmaline | Level 20

## Re: Need help with coding

Sir @Tom  I didn't pay the kind of attention to detail like you did. I wholeheartedly admit, It was a cheeky assumption

Super User

## Re: Need help with coding

This feels a little like a jeopardy game puzzle.  Am I supposed to first supply the question before I give the code?

Sounds like your question is how do I right align the values in a set of variables.

Is the issue is to move all of the non-missing values to the right?

``````data have;
id+1;
input (net1-net3) (:\$3.);
datalines;
HMO POS PPO
POS PPO .
PPO .   .
. PPO .
;
proc print;
title 'HAVE';
run;

data want ;
set have;
array net net1-net3;
nmiss=0;
do loc=dim(net) to 1 by -1 ;
do nmiss=nmiss to loc-1 while (missing(net[loc-nmiss])); end;
if 0 < (loc-nmiss) then net[loc]=net[loc-nmiss];
else net[loc]=' ';
end;
drop nmiss loc;
run;

proc print;
title 'WANT';
run;
title;
``````

Obsidian | Level 7

## Re: Need help with coding

Thank for your quick response - but - unfortunately it is not working.
This is the output when I ran your code:
Obs ID NET1 NET2 NET3

1 HMO POS PPO
2 POS PPO
3 PPO
This is what I am trying to get:
ID NET1 NET2 NET3
ID1 HMO POS PPO
ID2 POS PPO
ID3 PPO

Super User

## Re: Need help with coding

@asabou01 wrote:
Thank for your quick response - but - unfortunately it is not working.
This is the output when I ran your code:
Obs ID NET1 NET2 NET3

1 HMO POS PPO
2 POS PPO
3 PPO
This is what I am trying to get:
ID NET1 NET2 NET3
ID1 HMO POS PPO
ID2 POS PPO
ID3 PPO

I thought you want to change where the missing values were?  This print out look like your original input.

Please post some example data, preferable in the form of a simple data step that can be copied and run.  Make sure to show the expected output for the given example, again in a way that is reproducible.

Make sure when posting code to use the Insert SAS code button the editor window.

Super User

## Re: Need help with coding

``````data temp;
infile datalines truncover;
input  ID :\$3. (net1-net3) (:\$3.);
datalines;
ID1  HMO  POS   PPO
ID2  POS   PPO
ID3  PPO
;
data want;
set temp;
array x{*} \$ net1-net3 ;
array y{*} \$ 80 _net1-_net3 ;
n=dim(x)+1;
do i=dim(x) to 1 by -1;
if not missing(x{i}) then do;n+(-1);y{n}=x{i};end;
end;
drop i n net1-net3;
run;
``````
Obsidian | Level 7

## Re: Need help with coding

Hi again, I am sorry to take your time again but your wonderful solution only worked partly and when I applied it to more observation it did not work.
Attached are samples with more obs. I appreciate it if you can take a second look.
Super User

## Re: Need help with coding

Post your new data and new output . and I could offer new code .

Obsidian | Level 7

## Re: Need help with coding

``````data want;
set hh.test;
array x1{*} \$ net1-net7 ;
array x2{*} count1-count7;
array x3{*} allow1-allow7;

array y1{*} \$ 80 _net1-_net7 ;
array y2{*}  _count1-_count7 ;
array y3{*}  _allow1-_allow7;

n1=dim(x1)+1; n2=dim(x2)+1; n3=dim(x3)+1;

do i=dim(x1) to 1 by -1;
if not missing(x1{i}) then do;
n1+(-1);y1{n1}=x1{i};
end;
end;

do j=dim(x2) to 1 by -1;
if not missing(x2{j}) then do;
n2+(-1);y2{n2}=x2{j};
end;
end;

do k=dim(x3) to 1 by -1;
if not missing(x3{k}) then do;
n3+(-1);y3{n3}=x3{k};
end;
end;
drop i j k n1 n2 n3 net1-net7 count1-count7 allow1-allow7;
run;

proc export data=want
outfile="c:\want.xlsx" replace
dbms=excel;
run;``````
Discussion stats
• 10 replies
• 837 views
• 2 likes
• 4 in conversation