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:
I would like to have this in one sas program, not 2 separate ones.
Thank you in advance,
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.
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;
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;
This works, but can you help me find a way to export something like this?
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).
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;
count=Count-1; to exclude the header row.
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.