How to parameterize the counter for browsing week numbers

[Marwa_Se]

Hello !

Every week i have to import a new file which contains data of the last week (for example for this week I have a set of data of the week 30), while keeping the previous imports of the others previous weeks,

The classic way is to add every time a proc import and change the name of the file:

/* week 1 */
proc import out = bib.Objindiv_01  	datafile = "C:\Users\username\Desktop\mywork\data\ObjHebd_20181.xlsx" 	dbms = excel replace; getnames = yes; run;

/* week 2 */
proc import out = bib. Objindiv_02  datafile = "C:\Users\username\Desktop\mywork\data\ObjHebd_20182.xlsx" 	dbms = excel replace; getnames = yes; run;

/* week 3 */
proc import out = bib. Objindiv_03	datafile = "C:\Users\username\Desktop\mywork\data\ObjHebd_20183.xlsx" 	dbms = excel replace; getnames = yes; run;

/* week 4 */
proc import out = bib. Objindiv_04  datafile = “"C:\Users\username\Desktop\mywork\data \ObjHebd_20184.xlsx" 	dbms = excel replace; getnames = yes; run;

But this make the program very long and heavy to execute, In December for example I will have 52 proc import to execute…

So I wanted to create a macro program for browsing the number of week as the following query :

data _null_;

do i=FIRST WEEK OF THE YEAR to THE LAST CURRENT WEEK;
	import_date=cat(put(year(intnx("month",today()-2,i)),z4.),month(intnx("month",today()-2,i ) ) );

	call execute(cats('proc import datafile="C:\Users\username\Desktop\mywork\data\ObjHebd_',
	import_date,'.xlsx" out=swork.Objectifs_',import_date,';run;'));

end;

run;

The question is how to parameterize then counter in order to start with the first week of the year until the current week?

Can u help me please?

Marwa

[RW9]

So you just want to import all files which are in a single directory?  I would mention at this point, that not using Excel format would simplfy your programming vastly, not just this step but having to process it afterwards due to guessed file structure (Excel has no control like SAS, so a numeric one time, might be character another etc.).

Anyways to import all files in a directory:

filename tmp pipe 'dir "c:/test/*.xlsx" /b';

data _null_;
  infile tmp dlm="¬";
  input;
  call execute(cats('proc import file="',_input_,'" dbms=excel out=bib.objindiv_",put(_n_,best.),"; run;'));
run;

Note not tested!  What this does is read in a directory of xlsx files, and for each filename creates a proc import step for each one, and outputs dataset with _n_ as the incremented.

Note this assumes windows and that you have command line access.

[Marwa_Se]

Thank you for your answer, i tried your code but i didn't understand the { put (_n_, best) }  ?

[RW9]

_N_ is a SAS automatic variable.  It is the observation number, so think of it like;

Row 1 in dataset _n_=1
Row 2 in dataset _n_=2
...

I take this number and add it to the output dataset name, so that each iteration, and import has a different output dataset name.

[Marwa_Se]

i don't know why but it doesn't work ..

[RW9]

I am not sure how I could advise with no information.  What doesn't work?  Do you get errors/warnings in the log?  Show the code you are running, and the log out from it.

[Marwa_Se]

I'm not sure if what i wrote was right! this is my program:

filename tmp pipe 'dir "C:\Users\vfselma\Desktop\DDE SIEGE\CDG\Pilotage commercial Perso\SAS\TEST/*.xlsx" /b';

data _null_;
  infile tmp dlm="¬";
  input;
  call execute(cats('proc import file="C:\Users\vfselma\Desktop\DDE SIEGE\CDG\Pilotage commercial Perso\SAS\TEST" dbms=excel out=swork.objindiv_",put(_n_,best.),"; run;'));
run;

and this what was written in the log

NOTE: The infile TMP is:
      Périphérique d'accès au pipe non nommé,

      PROCESS=dir "C:\Users\vfselma\Desktop\DDE SIEGE\CDG\Pilotage commercial
      Perso\SAS\TEST/*.xlsx" /b,
      RECFM=V,LRECL=256

Sortie Stderr :
Fichier introuvable
NOTE: 0 records were read from the infile TMP.
NOTE: L'étape DATA used (Total process time):
      real time           0.20 secondes
      cpu time            0.03 secondes

[RW9]

Your missing filename read in from the infile:

filename tmp pipe 'dir "C:\Users\vfselma\Desktop\DDE SIEGE\CDG\Pilotage commercial Perso\SAS\TEST/*.xlsx" /b';

data _null_;
  infile tmp dlm="¬";
  input;
  call execute(cats('proc import file="C:\Users\vfselma\Desktop\DDE SIEGE\CDG\Pilotage commercial Perso\SAS\TEST\',_input_,'" dbms=excel out=swork.objindiv_',put(_n_,best.),'; run;'));
run;

[mkeintz]

If the only change in data is the most recent week, and all prior weeks are unchanged, why not create a permanent SAS dataset, containing all weeks year-to-date?  Then your weekly program would import only the most recent week, and append its data to the master sas dataset. 

It would also mean your analysis/reporting processes would only have to treat one dataset for all current-year tasks.

[Marwa_Se]

My problem wasn't really obvious. Actually, i wanted to create a macro variable that contains all the week numbers since the beginning of the year. In order to import files which are sorted by the week number...

proc import out = work.ObjCollab_01  (drop=username Actifs) 	datafile = "\\....\ObjHebd_20181.xlsx" 	dbms = excel replace; getnames = yes; run;

proc import out = work.ObjCollab_02  (drop=username Actifs) 	datafile = "\\\\....\ObjHebd_20182.xlsx" 	dbms = excel replace; getnames = yes; run;

proc import out = work.ObjCollab_03  (drop=username Actifs) 	datafile = "\\....\ObjHebd_20183.xlsx" 	dbms = excel replace; getnames = yes; run;

proc import out = work.ObjCollab_04  (drop=username Actifs) 	datafile = "\\....\ObjHebd_20184.xlsx" 	dbms = excel replace; getnames = yes; run;

proc import out = work.ObjCollab_05  (drop=username Actifs) 	datafile = "\\....\ObjHebd_20185.xlsx" 	dbms = excel replace; getnames = yes; run;

proc import out = work.ObjCollab_06  (drop=username Actifs) 	datafile = "\\....\ObjHebd_20186.xlsx" 	dbms = excel replace; getnames = yes; run;

proc import out = work.ObjCollab_07  (drop=username Actifs) 	datafile = "\\....\ObjHebd_20187.xlsx" 	dbms = excel replace; getnames = yes; run;

proc import out = work.ObjCollab_08  (drop=username Actifs) 	datafile = "\\....\ObjHebd_20188.xlsx" 	dbms = excel replace; getnames = yes; run;

proc import out = work.ObjCollab_09  (drop=username Actifs) 	datafile = "\\....\ObjHebd_20189.xlsx" 	dbms = excel replace; getnames = yes; run;

Etc. etc. etc. 

So instead of having 52 lines of proc import for each file every week, i want to create a macro variable that would replace the week number in one proc import...

I