BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ebwest
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

View solution in original post

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 708 views
  • 0 likes
  • 2 in conversation