Need help with loop

ebwest — Thu, 31 Jul 2014 13:32:14 GMT

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;

Re: Need help with loop

RW9 — Thu, 31 Jul 2014 13:56:33 GMT

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;

Re: Need help with loop

ebwest — Thu, 31 Jul 2014 14:32:55 GMT

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;

topic Need help with loop in SAS Programming

Need help with loop

Re: Need help with loop

Re: Need help with loop