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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.