topic Re: How to have maximum of one variable of all files in one folder? in SAS Programming

How to have maximum of one variable of all files in one folder?

Phil_NZ — Mon, 19 Apr 2021 21:29:19 GMT

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.

Re: How to have maximum of one variable of all files in one folder?

Reeza — Mon, 19 Apr 2021 21:51:47 GMT

This is where you need documentation not necessarily more programming. If you don't have it, then you're stuck doing it manually, ie scanning all files and then essentially creating a documentation file for yourself.

Re: How to have maximum of one variable of all files in one folder?

mkeintz — Tue, 20 Apr 2021 00:42:07 GMT

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;

Re: How to have maximum of one variable of all files in one folder?

Phil_NZ — Tue, 20 Apr 2021 01:27:06 GMT

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.

Re: How to have maximum of one variable of all files in one folder?

Phil_NZ — Tue, 20 Apr 2021 02:25:38 GMT

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.

Re: How to have maximum of one variable of all files in one folder?

mkeintz — Tue, 20 Apr 2021 09:44:40 GMT

I used "(KEEP=AGE" in my test program, and forgot to modify it for your example. In other words, an oversight.

Re: How to have maximum of one variable of all files in one folder?

Kurt_Bremser — Tue, 20 Apr 2021 11:42:10 GMT

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;

Re: How to have maximum of one variable of all files in one folder?

ballardw — Tue, 20 Apr 2021 15:09:31 GMT

@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.