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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.