Hi everyone, I have a data set with about 100 variables and each of them has a different format because the codes in the variables are different. Problem: I don't know in advance how many variables I have to report on and each variable needs its related format. So if I have to query 25 variables, I have to apply 25 formats in my final output.   I have a solution that works and I reproduced it partially below Let's assume that ALLSTATES is my large file and and I have to work on the subset called myfile.   I wrote a macro based on if statements to assign each formats but this solution has a cost: an extra file is created at each if statement.So if have 60 variables, I will get 60 extra files.   Is there a better way to do this and to avoid to get so many files?   DATA ALLSTATES; CANADIAN="ON"; NORTH_E="NJ"; WEST_C="WA"; output; CANADIAN="QC"; NORTH_E="NY"; WEST_C="CA"; output; CANADIAN="MB"; NORTH_E="VE"; WEST_C="NE"; output; run;   proc format ; VALUE $CAN (NOTSORTED) "ON"="ONTARIO" "QC"="QUEBEC" "MB"="MANITOBA"; run; proc format ; VALUE $NORTHE (NOTSORTED) "NJ"="NEWJERSEY" "NY"="NEWYORK" "VE"="VERMONT"; run; proc format ; VALUE $WEST (NOTSORTED) "WA"="WASHINGTON" "CA"="CALIFORNIA" "NE"="NEVADA"; run;   DATA myfile ; set ALLSTATES; drop west_c; run;   %macro formatmyvars(ds); %local dsid ; %let dsid = %sysfunc(open(&ds)); %if %sysfunc(varnum(&dsid, CANADIAN)) > 0 %then %do; data myfile2; set myfile; newv_CANADIAN=put(CANADIAN,$CAN.); run; %end; %else %do; data myfile2; set myfile;run; %end; %if %sysfunc(varnum(&dsid, NORTH_E)) > 0 %then %do; data myfile3; set myfile2; newv_NORTH_E=put(NORTH_E,$NORTHE.); run; %end; %else %do; data myfile3; set myfile2;run; %end; /*and so on*/ %mend formatmyvars; %formatmyvars(myfile ); ... View more

Hi all, I need 41 files from Y1980 to Y2020 with one variable (index) going from year*1000 to year*1000+900. So for Y1980, Index will range from 19800000 to 19800900. I wrote the following but the result is not as expected.   %macro myloop; %do i=1980 %to 2020; data Y&i; %do index=&i*1000 %to &i*1000+900; output; %end; run; %end; %mend;   %myloop;       ... View more

Hi all,   I have a file having 6 company names and I want to loop through the names with like to return values from another file (phone number, email, etc). The first problem is I have to loop through the file to get the company names; they are entered kind of manually in the macro now. The second problem is the syntax in the where statement. %macro Rollthecomp; %let condition1=GYSERVICESINC; %let condition2=MECH; %let condition3=ALCOOP; %let condition5=RTAGESYSTEMS; %let condition4=SCHEMICALS; %let condition6=IORPROPANE; %do i =1 %to 6; proc sql; create table listcarriers&&i as select phonenumber, email from mycompanyfile where compress(uniquename) like "&&condition&&i%"; quit; %end; %mend; ... View more

Hi all, I want to extract (from a variable called textvar) the following expression : the two letters UN followed by 4 numbers. This means from 'UN0001' to "UN9999". I need help with the prx because I found examples are about numbers or letters but not both ! data want ; set have ; num = prxchange('   ', textvar); newvar=num; run;   Thanks for your help. ... View more