Hi all, I am wondering how can I have the maximum of one variable x2 from all SAS files in one folder (this folder only contains 64 SAS files) ?
The code that I run to get the contents of all files in this folder is
Libname input_e 'C:\Users\pnguyen\OneDrive - Massey University\PhD JOURNEY\leniency & operating\2Cfiltering' access=readonly;
proc contents data=INPUT._ALL_ out=contents;
run;
Warm regards.
You can use the dictionary facility of proc sql to find all the datasets with a variable named x2. Put those dataset names into a list to make a data set view, and run proc means to get the stats on X2 for all appropriate datasets:
proc sql noprint;
select cats(memname,'(keep=age)') into :dsnames separated by ' '
from dictionary.columns
where libname='INPUT_E' and upcase(name)='X2';
quit;
%put &=dsnames ;
data need/view=need;
set &dsnames;
run;
proc means data=need;
run;
Hi @mkeintz
Thank you for your helps,
There are two points here I am curious about:
1. I am wondering why you use
(keep=age)
2. The code has something wrong about the libname, I fund no reason why it gets wrong
39 proc sql noprint;
40 select cats(memname,'(keep=age)') into :dsnames separated by ' '
41 from dictionary.columns
42 where libname='INPUT_E' and upcase(name)='S2';
43 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
44 %put &=dsnames ;
DSNAMES=ARGENTINA_MERGE2(keep=age) AUSTRALIA_MERGE2(keep=age) AUSTRIA_MERGE2(keep=age) BELGIUM_MERGE2(keep=age)
BRAZIL_MERGE2(keep=age) BULGARIA_MERGE2(keep=age) CANADA_MERGE2(keep=age) CHILE_MERGE2(keep=age) CHINA_MERGE2(keep=age)
COLOMBIA_MERGE2(keep=age) CROATIA_MERGE2(keep=age) CYPRUS_MERGE2(keep=age) CZECH_MERGE2(keep=age) DENMARK_MERGE2(keep=age)
ECUADOR_MERGE2(keep=age) ESTONIA_MERGE2(keep=age) FINLAND_MERGE2(keep=age) FRANCE_MERGE2(keep=age) GERMANY_MERGE2(keep=age)
GREECE_MERGE2(keep=age) HONGKONG_MERGE2(keep=age) HUNGARY_MERGE2(keep=age) ICELAND_MERGE2(keep=age) INDIA_MERGE2(keep=age)
INDONESIA_MERGE2(keep=age) IRELAND_MERGE2(keep=age) ISRAEL_MERGE2(keep=age) ITALY_MERGE2(keep=age) JAPAN_MERGE2(keep=age)
JORDAN_MERGE2(keep=age) KENYA_MERGE2(keep=age) LATVIA_MERGE2(keep=age) LITHUANIA_MERGE2(keep=age) LUXEMBOURG_MERGE2(keep=age)
MALAYSIA_MERGE2(keep=age) MEXICO_MERGE2(keep=age) NETHERLANDS_MERGE2(keep=age) NEWZEALAND_MERGE2(keep=age) NIGERIA_MERGE2(keep=age)
NORWAY_MERGE2(keep=age) OMAN_MERGE2(keep=age) PAKISTAN_MERGE2(keep=age) PERU_MERGE2(keep=age) PHILIPPINES_MERGE2(keep=age)
POLAND_MERGE2(keep=age) PORTUGAL_MERGE2(keep=age) ROMANIA_MERGE2(keep=age) RUSSIAN_MERGE2(keep=age) SINGAPORE_MERGE2(keep=age)
SLOVAKIA_MERGE2(keep=age) SLOVENIA_MERGE2(keep=age) SOUTHAFRICA_MERGE2(keep=age) SOUTHKOREA_MERGE2(keep=age) SPAIN_MERGE2(keep=age)
SWEDEN_MERGE2(keep=age) SWITZERLAND_MERGE2(keep=age) THAILAND_MERGE2(keep=age) TURKEY_MERGE2(keep=age) UKRAINE_MERGE2(keep=age)
UNITEDK_MERGE2(keep=age) UNITEDS_MERGE2(keep=age) VENEZUELA_MERGE2(keep=age) VIETNAM_MERGE2(keep=age) ZAMBIA_MERGE2(keep=age)
45
46 data need/view=need;
47 set &dsnames;
ERROR: File WORK.ARGENTINA_MERGE2.DATA does not exist.
ERROR: File WORK.AUSTRALIA_MERGE2.DATA does not exist.
ERROR: File WORK.AUSTRIA_MERGE2.DATA does not exist.
ERROR: File WORK.BELGIUM_MERGE2.DATA does not exist.
ERROR: File WORK.BRAZIL_MERGE2.DATA does not exist.
ERROR: File WORK.BULGARIA_MERGE2.DATA does not exist.
ERROR: File WORK.CANADA_MERGE2.DATA does not exist.
Warm regards.
I used "(KEEP=AGE" in my test program, and forgot to modify it for your example. In other words, an oversight.
Hi @mkeintz
I did adjust your code and I stood at the current place
Libname input_e 'C:\Users\pnguyen\OneDrive - Massey University\PhD JOURNEY\leniency & operating\2Cfiltering' access=readonly;
proc contents data=INPUT_E._ALL_ out=contents;
run;
proc sql noprint;
select catx('.',libname,memname) into :dsnames separated by ' '
from dictionary.columns
where libname='INPUT_E' and upcase(name)='S2';
quit;
%put &=dsnames ;
Now, I have a list of libname and memname. What I want is to get the max of S2 in each file in this folder rather than concatenate all file and got the maximum of s2 in the concatenating file.
44 %put &=dsnames ;
DSNAMES=INPUT_E.ARGENTINA_MERGE2 INPUT_E.AUSTRALIA_MERGE2 INPUT_E.AUSTRIA_MERGE2 INPUT_E.BELGIUM_MERGE2 INPUT_E.BRAZIL_MERGE2
INPUT_E.BULGARIA_MERGE2 INPUT_E.CANADA_MERGE2 INPUT_E.CHILE_MERGE2 INPUT_E.CHINA_MERGE2 INPUT_E.COLOMBIA_MERGE2
INPUT_E.CROATIA_MERGE2 INPUT_E.CYPRUS_MERGE2 INPUT_E.CZECH_MERGE2 INPUT_E.DENMARK_MERGE2 INPUT_E.ECUADOR_MERGE2
INPUT_E.ESTONIA_MERGE2 INPUT_E.FINLAND_MERGE2 INPUT_E.FRANCE_MERGE2 INPUT_E.GERMANY_MERGE2 INPUT_E.GREECE_MERGE2
INPUT_E.HONGKONG_MERGE2 INPUT_E.HUNGARY_MERGE2 INPUT_E.ICELAND_MERGE2 INPUT_E.INDIA_MERGE2 INPUT_E.INDONESIA_MERGE2
INPUT_E.IRELAND_MERGE2 INPUT_E.ISRAEL_MERGE2 INPUT_E.ITALY_MERGE2 INPUT_E.JAPAN_MERGE2 INPUT_E.JORDAN_MERGE2 INPUT_E.KENYA_MERGE2
INPUT_E.LATVIA_MERGE2 INPUT_E.LITHUANIA_MERGE2 INPUT_E.LUXEMBOURG_MERGE2 INPUT_E.MALAYSIA_MERGE2 INPUT_E.MEXICO_MERGE2
INPUT_E.NETHERLANDS_MERGE2 INPUT_E.NEWZEALAND_MERGE2 INPUT_E.NIGERIA_MERGE2 INPUT_E.NORWAY_MERGE2 INPUT_E.OMAN_MERGE2
INPUT_E.PAKISTAN_MERGE2 INPUT_E.PERU_MERGE2 INPUT_E.PHILIPPINES_MERGE2 INPUT_E.POLAND_MERGE2 INPUT_E.PORTUGAL_MERGE2
INPUT_E.ROMANIA_MERGE2 INPUT_E.RUSSIAN_MERGE2 INPUT_E.SINGAPORE_MERGE2 INPUT_E.SLOVAKIA_MERGE2 INPUT_E.SLOVENIA_MERGE2
INPUT_E.SOUTHAFRICA_MERGE2 INPUT_E.SOUTHKOREA_MERGE2 INPUT_E.SPAIN_MERGE2 INPUT_E.SWEDEN_MERGE2 INPUT_E.SWITZERLAND_MERGE2
INPUT_E.THAILAND_MERGE2 INPUT_E.TURKEY_MERGE2 INPUT_E.UKRAINE_MERGE2 INPUT_E.UNITEDK_MERGE2 INPUT_E.UNITEDS_MERGE2
INPUT_E.VENEZUELA_MERGE2 INPUT_E.VIETNAM_MERGE2 INPUT_E.ZAMBIA_MERGE2
Warm regards.
Prepare a view of all data with this:
proc sql noprint;
select catx('.',libname,memname) !! " (keep=s2)" into :dsnames separated by ' '
from dictionary.columns
where libname='INPUT_E' and upcase(name)='S2';
quit;
data all / view=all;
length dsname $32 dname $41;
set &dsnames indsname=dname;
dsname = scan(dname,2,".");
run;
Then run one of these two steps:
proc sql;
create table want as
select
dsname,
max(s2) as s2_max
from all
group by dsname
;
quit;
proc means data=all;
class dsname;
var s2;
output out=want max()=;
run;
@Phil_NZ wrote:
Now, I have a list of libname and memname. What I want is to get the max of S2 in each file in this folder rather than concatenate all file and got the maximum of s2 in the concatenating file.
Note the the highlight above is not what you requested in the first post: (emphasis added)
Hi all, I am wondering how can I have the maximum of one variable x2 from all SAS files in one folder
Clearly stating the actual objective gets desired solutions quicker.
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.