topic Re: Utilisation de macro variables pour INTNX : ne me retourne rien in SAS Procedures

Utilisation de macro variables pour INTNX : ne me retourne rien

alisondu77 — Wed, 30 Mar 2016 12:57:51 GMT

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 🙂

Alison

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

RW9 — Wed, 30 Mar 2016 13:59:11 GMT

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.

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

LinusH — Wed, 30 Mar 2016 14:00:48 GMT

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.

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

alisondu77 — Thu, 31 Mar 2016 07:02:02 GMT

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

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

RW9 — Thu, 31 Mar 2016 09:04:55 GMT

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.);

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

alisondu77 — Thu, 31 Mar 2016 12:11:39 GMT

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 🙂

So this is the final version (with comment in French sorry 🙂 😞

%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 !!! 🙂

Have a good day RW9 🙂