topic Re: Need help with coding in SAS Programming

Need help with coding

asabou01 — Sat, 07 Mar 2020 21:21:47 GMT

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

Re: Need help with coding

novinosrin — Sat, 07 Mar 2020 21:37:09 GMT



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

Re: Need help with coding

Tom — Sat, 07 Mar 2020 22:29:20 GMT

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;

Re: Need help with coding

Tom — Sat, 07 Mar 2020 22:30:57 GMT

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

Re: Need help with coding

novinosrin — Sat, 07 Mar 2020 22:33:01 GMT

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

Re: Need help with coding

Ksharp — Sun, 08 Mar 2020 04:38:15 GMT

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;

Re: Need help with coding

asabou01 — Sun, 08 Mar 2020 13:37:14 GMT

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

Re: Need help with coding

asabou01 — Sun, 08 Mar 2020 16:25:14 GMT

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.

Re: Need help with coding

Tom — Sun, 08 Mar 2020 16:53:43 GMT

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

Re: Need help with coding

asabou01 — Mon, 09 Mar 2020 00:07:54 GMT

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;

Re: Need help with coding

Ksharp — Mon, 09 Mar 2020 05:35:47 GMT

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