Need help with loop

[ebwest]

Good morning,

I am hoping someone can help me with a loop I'm trying to put together.  My code below works, and does the following:  1. creates a table with a count of how many codes there are per idnum/date grouping 2. stores the maximum count in a macro variable called varcount (this should be the maximum number of columns I would have when the data is transposed to have one row per unique idnum/date grouping and codes in additional columns) 3.  creates a list of all the unique codes in the file 4.  goes through every code in the code list and creates a new variable for each code named with "cd" and the actual code value.  5.  currently, i have written the code out for up to 14 columns (see green font below), and it works fine.  This just puts a 1 in the additional column created in the outer loop based on whether the code was present in columns 1 through 14.  I would like to make this into an inner loop that will go from 1 to varcount since varcount is the maximum number of columns that would exist.  I have tried a few things, but nothing has worked, so I went back to what worked so I could at least show what I am trying to do.  Can anyone help me understand how to do this?  Any help is greatly appreciated.  I am fairly new to some of this.

options;
%include "C:\SAS Information\Macros\for.sas";

%let list = [work.srvc_cd_list];

proc sql;
create table sasuser.codespercase as (
select count(srvc_cd)as Count
from sasuser.sample_file
group by idnum, date);
quit;

proc sql noprint;
select max(count)
into :varcount
from sasuser.codespercase;
quit;

proc sql;
create table srvc_cd_list as
(select distinct srvc_cd
from sasuser.sample_file);
quit;
data work.new;
set SASUSER.TRNSTRANSPOSEDSAMPLE_file;
%for(srvc_cd, in=&list, do=%nrstr(
length cd&srvc_cd 3.;
if input(column1,comma10.)=&srvc_cd then cd&srvc_cd = 1;
if input(column2,comma10.)=&srvc_cd then cd&srvc_cd = 1;
if input(column3,comma10.)=&srvc_cd then cd&srvc_cd = 1;
if input(column4,comma10.)=&srvc_cd then cd&srvc_cd = 1;
if input(column5,comma10.)=&srvc_cd then cd&srvc_cd = 1;
if input(column6,comma10.)=&srvc_cd then cd&srvc_cd = 1;
if input(column7,comma10.)=&srvc_cd then cd&srvc_cd = 1;
if input(column8,comma10.)=&srvc_cd then cd&srvc_cd = 1;
if input(column9,comma10.)=&srvc_cd then cd&srvc_cd = 1;
if input(column10,comma10.)=&srvc_cd then cd&srvc_cd = 1;
if input(column11,comma10.)=&srvc_cd then cd&srvc_cd = 1;
if input(column12,comma10.)=&srvc_cd then cd&srvc_cd = 1;
if input(column13,comma10.)=&srvc_cd then cd&srvc_cd = 1;
if input(column14,comma10.)=&srvc_cd then cd&srvc_cd = 1;
))
run;

[RW9 · Accepted solution]

Hi,

Not seeing any test data doesn't help, but it would appear that you are trying to get a transposed table.  Check out the transpose function.

Alternatively arrays:

proc sql;

     select     count(distinct NAME)

     into          :TOTAL_VARS

     from          SAMPLE_FILE;

quit;

data want;

     set have;

     array cd{&TOTAL_VARS.} 1.;

     array column(&TOTAL_VARS.);

     do I=1 to &TOTAL_VARS.;

          if COLUMN{I}=&SRVC_CD. then CD{I}=1;

     end;

run;

[ebwest]

Thank you.  I did some further reading on arrays, and tweaked that block of code like below.  This worked.  Thanks again for your help.

data work.new;

set SASUSER.TRNSTRANSPOSEDSAMPLE_CLAIMS;

array column(&varcount);

%for(srvc_cd, in=&list, do=%nrstr(

length cd&srvc_cd 3.;

do i=1 to &varcount.;

  if input(column{i},comma10.)=&srvc_cd then cd&srvc_cd = 1;

end;

))

run;