topic Re: How to get files with different names, infile, then export? in SAS Programming

How to get files with different names, infile, then export?

leeleelee — Thu, 23 Sep 2021 08:27:31 GMT

Hello, I have the following files in [location] that are named:

cookies-2015_updated
cookies-2016_updated
cookies-2017
cookies2018

How do I infile these to:

Make sure the individual files get exported to individual sas datasets?
Export excel file with record counts using proc sql?

I would like to have this in one sas program, not 2 separate ones.

Thank you in advance,

Re: How to get files with different names, infile, then export?

Patrick — Thu, 23 Sep 2021 09:54:42 GMT

What have you tried so far? Where did you get stuck?

If asking for code please provide sample data and clearly show how the desired output using the sample data should look like.

Re: How to get files with different names, infile, then export?

leeleelee — Thu, 23 Sep 2021 20:28:12 GMT

I got this far:

libname cookies "C:\Output_filepath";

%let cook_file = C:\Data_filepath;

%macro file_yr (year=year);

data cookies.cookies-&year.;
/* wondering about this statement */

infile "&cook_file.\cookies-&year..txt"

	firstobs = 2 dlm="|" lrecl=32767 missover truncover dsd;

/* inserted attributes/field headers here
input
;

format
inserted formatting for variables here as well
;
*/

run;
%mend;

%file_yr 			(year=2015);
%file_yr 			(year=2016);
%file_yr 			(year=2017);
%file_yr 			(year=2018);
/* unsure how to change the %fire_yr here to accommodate for the "Updated" files*/

/* Stack all years */
data cookies_All_Years;
	set 
Cookies.cookies_2015
Cookies.cookies_2016
Cookies.cookies_2017
Cookies.cookies_2018
;
run;

/* record count check */
proc sql;
	create table cookies_cnts as
	select year,
    count (*) as Num_Records
from   cookies_all_years
group by year
;
quit;

proc export 
  data=cookies_cnts
  dbms=xlsx 
  outfile="C:\cookie record counts.xlsx"
  replace;
run;

Re: How to get files with different names, infile, then export?

Patrick — Thu, 23 Sep 2021 22:54:28 GMT

Would below do what you're after?


/* create sample data */
%let cook_file = C:\temp;
filename sample "&cook_file";
data _null_;
  file sample(cookies-2015_updated.txt);
  put 'colA|colB';
  put 'valueA|123';
  file sample(cookies-2016.txt);
  put 'colA|colB';
  put 'valueA|456';
  put 'valueA|456';
  file sample(cookies-2017.txt);
  put 'colA|colB';
  put 'valueA|789';
  put 'valueA|789';
  put 'valueA|789';
run;


/* proposed code based on posted one*/

libname cookies "C:\temp";
%let cook_file = C:\temp;

data cookies_All_Years;
  length _ThisFile $100;
  infile "&cook_file.\cookies-*.txt" dlm="|" lrecl=32767 truncover dsd filename=_ThisFile;
  input @;

  /* skip first line of a new input file */
  if lag(_ThisFile) ne _ThisFile then delete;

  /* read data */
  input colA $ colB;
  Source_File=_ThisFile;
run;

proc sql;
	create table cookies_cnts as
	select Source_File,
    count (*) as Num_Records
  from   cookies_all_years
  group by Source_File
;
quit;

data cookies_All_Years;
  merge cookies_All_Years cookies_cnts;
  by Source_File;
  Year=input(compress(scan(Source_File,-2,'\/-.'),'kd'),4.);
run;

proc export 
  data=cookies_cnts
  dbms=xlsx 
  outfile="C:\temp\cookie_record_counts.xlsx"
  replace;
run;

Re: How to get files with different names, infile, then export?

leeleelee — Fri, 24 Sep 2021 05:02:30 GMT

This works, but can you help me find a way to export something like this?

Re: How to get files with different names, infile, then export?

Patrick — Fri, 24 Sep 2021 06:08:12 GMT

The code I've posted already creates an Excel as below. Look into the scan() function if you only want to keep the file name and not path and file name.

Btw: If you don't need the detail data then you wouldn't need to create the table with all the detail but you would just count rows there and output whenever a new file starts or the very last record gets read (end=_last in the infile statement would allow to create such an end of data indicator).

Re: How to get files with different names, infile, then export?

Kurt_Bremser — Fri, 24 Sep 2021 06:37:01 GMT

On UNIX/Linux, I'd simply do

data files;
infile "wc -l &path./cookie*" pipe;
input filename :$200. count;
run;

proc export
  data=files
  file="path_to_result.xlsx"
  dbms=xlsx
  replace
;
run;

Re: How to get files with different names, infile, then export?

Patrick — Fri, 24 Sep 2021 06:48:53 GMT

@Kurt_Bremser

count=Count-1; to exclude the header row.