Hi SAS Users,
I have a problem when trying to run the macro ImportAndTranspose for all files in one folder (named New Folder). The filename is the name of countries with an under space, for example: Argentina_, Austria_, Australia_, Belgium_, US_, UK_,..... and I have 64 files like that.
The code is as below:
filename mydir 'C:\Users\pnguyen\Desktop\New folder';
data _null_;
did = dopen('mydir');
do i = 1 to dnum(did);
fname = dread(did,i);
short_fn= cats(substr(fname, 1,3),'_');
cmd=cats('%ImportAndTranspose(File=C:\Users\pnguyen\Desktop\New folder\',
strip(fname),',outf=',short_fn,'sheet,startsheet=1,endsheet=45);');
call execute(cmd);
end;
keep fname;
run;
Now I just recognize that if I do
short_fn= cats(substr(fname, 1,3),'_');
So, the short_fn for Australia and Austria or Slovakia and Slovenia are the same, but if I did
short_fn= cats(substr(fname, 1,4),'_');
UK_ and US_ do not have enough character in their names, and it make make the results' name in general not aesthetic.
Can you please give me any suggestion to adjust the code to solve the problem?
Many thanks and warm regards.
Ok. Obviously, you can do this with simple if-then logic. I will spare you the details.
For maintainability and length control, this is a good case using user-defined Function Logic in Proc Format. Here, I simply make 'special' cases for some countries, while the rest is sent to the function f, which does the work. Then all that is needed is a Put Function in a data step.
Feel free to ask 🙂
data have;
input fname $10.;
datalines;
Australia
Austria
Slovakia
UK
US
;
proc fcmp outlib=work.f.f;
function f(s $) $;
length r $ 4;
r = upcase(cats(substr(s, 1, 3), '_'));
return (r);
endsub;
run;
options cmplib = work.f;
proc format;
value $ country (default = 12)
'Australia' = 'Austra_'
'Austria' = 'Austri_'
'UK' = 'UK_'
'US' = 'US_'
other = [f()]
;
run;
data want;
set have;
short_fn = put(fname, $country.);
run;
Try something like this
data have;
input fname $10.;
datalines;
Australia
Austria
Slovakia
UK
US
;
data want;
set have;
short_fn = cats(substr(fname, 1, min(3, length(fname))), '_');
run;
The result is as below:
I mean, one reasonable solution is to get Austra_ for Australia, Austri_ for Austria, UK_ for the UK, US_ for the US, and the other files are normal for example: ARG_ for Argentina, CHI_ for China, so on and so forth.
Many thanks!
Ok. Obviously, you can do this with simple if-then logic. I will spare you the details.
For maintainability and length control, this is a good case using user-defined Function Logic in Proc Format. Here, I simply make 'special' cases for some countries, while the rest is sent to the function f, which does the work. Then all that is needed is a Put Function in a data step.
Feel free to ask 🙂
data have;
input fname $10.;
datalines;
Australia
Austria
Slovakia
UK
US
;
proc fcmp outlib=work.f.f;
function f(s $) $;
length r $ 4;
r = upcase(cats(substr(s, 1, 3), '_'));
return (r);
endsub;
run;
options cmplib = work.f;
proc format;
value $ country (default = 12)
'Australia' = 'Austra_'
'Austria' = 'Austri_'
'UK' = 'UK_'
'US' = 'US_'
other = [f()]
;
run;
data want;
set have;
short_fn = put(fname, $country.);
run;
Replace the substr() function into scan() function:
short_fn = cats(scan(fname,1,'_'),'_');
Do this:
length shortfn $4;
shortfn = scan(fname,1,"_");
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.