Help using Base SAS procedures

Utilisation de macro variables pour INTNX : ne me retourne rien

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Utilisation de macro variables pour INTNX : ne me retourne rien

Bonjour,

Je rencontre actuellement un problème sous SAS et j'avoue que cela fait un bout de temps que je n'en ai pas fait...

 

Mon problème est le suivant :

1) je souhaite sauvegarder dans date_debut_douverture la date "01sep2013"d   : OK cela fonctionne

2) je souhaite calculer date_i = date_debut_douverture + i mois (via la fonction intnx : PB cela me retourne " " et non pas "01oct2013"d, "01nov2013"d etc, je n'ai pas d'avertissement sous SAS

3) je souhaiterais ensuite calculer date_table_i pour avoir respectivement un NOMBRE correspondant à YYMM : 1310, 1311, 1312, 1401 etc : PB ça ne fonctionne pas vu que l'étape 2 ne fonctionne pas... Cette variable me permettra ensuite de faire appel à un table de type WORK.A_1310, WORK.A_1311...

J'ai absolument besoin des deux variables date_i et date_table_i (date_i me servira pour d'autres fonctions que je n'ai pas mise dans mon code).

 

Voici mon script : 

%global date_debut_ouverture ;
%let date_debut_ouverture = "01sep2013"d;

/*MACRO pour calculer date_debut_ouverture + i mois*/
%macro calcul_date(nbmois);
    %do i=1 %to &nbmois;
        %global date_&i date_table_&i ;
        %let date_&i = %SYSFUNC(intnx("month", &date_debut_ouverture, &i, 'same'),date9.);
        %let date_table_&i = %SYSFUNC(putn(&date_&i.,yymmn4.),yymmn4.);      /*je pense qu'il manque tout de même l'étape où il y a la transformation de la date en chiffre */
    %end;
%mend calcul_date;
%calcul_date(24);
 

/*on lit les tables WORK.A_YYMM etc. */ 
%Macro fonction_table(YYMM);
  proc sql ; 
	create table work.sortie as select *
	FROM work.A_&YYMM ;
  quit ; 
%Mend fonction_table;

%fonction_table(&date_table_1);

Je vous remercie par avance de votre aide, et vous souhaite une bonne journée Smiley Happy

 

 

Alison


Accepted Solutions
Solution
‎03-31-2016 08:12 AM
Super User
Super User
Posts: 7,954

Re: Utilisation de macro variables pour INTNX : ne me retourne rien

Posted in reply to alisondu77

Well, I am unsure as to why you would have a restriction on what you do in your working area.  In the code you post, you are creating datasets with different names:

	create table work.sortie as select *

I am not suggesting changing your source data, only your copy of it. Anyways, going back to the first part of my post, you can do your calculation in a datastep and then push it out to the macro varaible.  

%let date_debut_ouverture="01SEP2013"d;
%let nbmois=1;

data _null_;
do i=1 to &nbmois;
dt=intnx("month",&date_debut_ouverture.,i,'same');
ym=put(year(dt),z4.)||put(month(dt),z2.);
call symput(cats('date',i),ym);
end;
run;

%put &date1;

 What this will do is to create one macro variable, each called dateX - X being the incrementor.  You can then use that further in your code:

%fonction_table(&date1.);

View solution in original post


All Replies
Super User
Super User
Posts: 7,954

Re: Utilisation de macro variables pour INTNX : ne me retourne rien

Posted in reply to alisondu77

Not sure why you would want to do all that "data" processing in macro language.  Datastep is designed to manipulate and process data for example has date types, macro just generates datastep code.  Something like:

%let date_debut_ouverture="01SEP2013"d;
%let nbmois=24;

data _null_;
  do i=1 to &nbmois;
    call symput(cats('date',i),put(intnx("month",&date_debut_overture.,i,'same'),date9.));
  end;
run;

However I would suggest two things.  First its not a good idea to put data - in this case month and year - as dataset names.  Append your data together, and create a column of data from that:

data total;
  set work.a_: indsname=nme;
  year_month=strip(tranwrd(nme,"A_",""));
run;

With the above, you then have one dataset, which you can process once, avoiding looping over many datasets, and if needed, you can split them up later on.  

Secondly, instead of making lots of macro variables, and then using them on lots of different datasets, you can set your range into a dataset and use simple merging:

data range;
  do year=2013 to 2015;
    do month=1 to 12;
      year_month=cats(substr(put(year,z4.),3,2),put(month,z2.));
      output;
    end;
  end;
run;

proc sql;
  create table WANT as
  select  *
  from    TOTAL
  where  YEAR_MONTH in (select YEAR_MONTH from RANGE);
quit;

Simpler coding.  Also allows you to use "by group" processing, which is quicker and better on resources than doing each group indivisually.

Occasional Contributor
Posts: 15

Re: Utilisation de macro variables pour INTNX : ne me retourne rien

Hello everyone,

Sorry my English is poor but thank you for your complete and quick answers.

I can’t change my dataset, I have millions of data in each sets. So I have to use the name of the sets “work.A_YYMM” to do my work. And it’s forbidden to me to change the datasets :/ sorry.

 

I will try to explain you what I want to do :

  • I save “01sept2013” in a variable : date_debut_ouverture : OK it’s work
  • Then I would like to calculate for i from 1 to 24 : date_debut_ouverture + I * month (save in the variable date_i). That’s to say I would like :     Date_1 = ‘01oct2013                    Date_2 = ’01nov2013’  ...                          Date_24 = ‘01aug2015’.

         To do this I choose the function : intnx --> (%let date_&i = %SYSFUNC(intnx("month", &date_debut_ouverture, &i, 'same'),date9.);

      But when I want to see the result, I have ‘ ‘ (so nothing). And I didn’t understand why and I have no errors in my log.

 

  • Then I would like to use the variable date_i, to transform it in a number which represent YYMM (save in the variable date_table_i), so for example :     Date_table_1 = 1310              Date_table_2 = 1311 …        Date_table_4 = 1508

I didn’t go so far in this point, because of the previous step (2) that didn’t work.

 

 

What I want to do with this, is to use date_table_i to call sets which looks like “A_YYMM”, and to use date_i for some sections where in some others proc sql like (where date=&date_i).

 

 

Thank you again, and have a good day !

 

Alison

Solution
‎03-31-2016 08:12 AM
Super User
Super User
Posts: 7,954

Re: Utilisation de macro variables pour INTNX : ne me retourne rien

Posted in reply to alisondu77

Well, I am unsure as to why you would have a restriction on what you do in your working area.  In the code you post, you are creating datasets with different names:

	create table work.sortie as select *

I am not suggesting changing your source data, only your copy of it. Anyways, going back to the first part of my post, you can do your calculation in a datastep and then push it out to the macro varaible.  

%let date_debut_ouverture="01SEP2013"d;
%let nbmois=1;

data _null_;
do i=1 to &nbmois;
dt=intnx("month",&date_debut_ouverture.,i,'same');
ym=put(year(dt),z4.)||put(month(dt),z2.);
call symput(cats('date',i),ym);
end;
run;

%put &date1;

 What this will do is to create one macro variable, each called dateX - X being the incrementor.  You can then use that further in your code:

%fonction_table(&date1.);
Occasional Contributor
Posts: 15

Re: Utilisation de macro variables pour INTNX : ne me retourne rien

Ohhhhh thank you so much ! It works very well ! Thank you, I was disappointed not to find the solution !

I made a little transformation of the solution to fit correctly on my datasets.

And you were right about the 'restriction', but it's because i simplify the code and so, it seems weird and like there are no restrictions Smiley Happy 

 

So this is the final version (with comment in French sorry Smiley Happy ): 

%global date_debut_ouverture nbmois;
%let date_debut_ouverture = "01sep2013"d;
%let nbmois = 24 ;

/*MACRO pour calculer date_debut_ouverture + i mois*/
data _null_;
 do i=1 to &nbmois;
 dt=intnx("month",&date_debut_ouverture.,i,'same');					/* date_debut_ouverture + i mois */
 dt_bis = put(dt,date9.) ;											/* dt sous format 01oct2013*/
 ym=substr(put(year(dt),z4.),3)||put(month(dt),z2.);				/* ym = 1310 (format 'YYMM') */
 call symput(cats('date_table_',i),ym);								/* enregistrement dans date_table_i = ym */
 call symput(cats('date_',i),dt_bis);								/* enregistrement dans date__i = dt */
 end;
run;
 

/*on lit les tables WORK.A_YYMM etc. */ 
%Macro fonction_table(YYMM);
  proc sql ; 
	create table work.sortie as select *
	FROM work.A_&YYMM ;												/* lecture de la table : work.A_1310 */
  quit ; 
%Mend fonction_table;

%fonction_table(&date_table_1);										

 

Thank you again !!!  Super answer !!! Smiley Happy

Have a good day RW9 Smiley Happy

 

Super User
Posts: 5,428

Re: Utilisation de macro variables pour INTNX : ne me retourne rien

Posted in reply to alisondu77

Sorry, but my school french is a bit rusty. Is it possible for you to translate your question to English?

If there's a need for submitting posts in french, try to convince your SAS office to start a french speaking forum. This already exists for German and Danish.

Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 466 views
  • 1 like
  • 3 in conversation