BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
alisondu77
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

alisondu77
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.);
alisondu77
Obsidian | Level 7

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 🙂

 

LinusH
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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