SAS Support Communities

Hello everyone, From a macro variable declared by a user (year), i want to have the first day of all the month of the period. For example if someone put : %let year = 2014 2015;  I want to have "01jan2014", "01feb2014", "01mar2014" ... "01nov2015" "01dec2015". I want to have this date save in different macro variable (date_1, date_2, date_3 ..., date_23, date_24).   So I have made a code, which works by half. I succeed in  having the "01mmm" but not the YYYY. %global year; %let year = 2014 2015 ; /* the user choose the year*/ %global nb_year; %let nb_year=%sysfunc(countw(&year.)); %put &nb_year. ; /*count the number of years*/ /* dates are saving in date_k (date_1 for "01jan2014", date_2 for '01feb2014"d ...) */ data _null_; k=0; ATTRIB month LENGTH=$5 ; do i=1 to (&nb_year.); year2 = scan(&year.,i,' ') ; /*this step doesn't work*/ do j=1 to 12 ; if j=1 then month= "01jan"; if j=2 then month="01feb"; if j=3 then month="01mar"; if j=4 then month="01apr"; if j=5 then month="01may"; if j=6 then month="01jun"; if j=7 then month="01jul"; if j=8 then month="01aug"; if j=9 then month="01sep"; if j=10 then month="01oct"; if j=11 then month="01nov"; if j=12 then month="01dec" ; k=k+1; ym=(month)||(year2); call symput(cats('date_',k),ym); end; end ; run ; %put &date_20.; So everything works except the lign where i declare "year2".   Can somenone help me to solve my problem ? Sorry for my poor english ...   Best regards,   Alison ... View more

Hello everyone, In a proc sql, I would like to test the existence of a table. This is my code (which doesn't work and i have no idea how to do it) :   proc sql ; create table union as if exist(table1) then ( select id1, . as id2, number from table1 ) union if exist(table2) then ( select '' as id1, id2, number from table2 ) ; quit ;  I have seen on the internet something like this, but i don't know how to put it in my case : %macro test(name); %if %sysfunc(exist(&name)) %then /* union of the 2 tables */ ; %mend test;   Thank you in advance, and sorry for my poor english 😕 ! Have a nice afternoon !     Alison   ... View more

Hello everyone, I have a little problem in my script SAS. I have a starting date (01jan2015), and I add to this date one month eleven times  (01jan2015, 01feb2015, ..., 01dec2015). There is no problem for that, but after I would like to count how many dates are in 2015 or 2014, and i don't succeed... I just have "1" in output for nb_month_2014 and nb_month_2015, which is wrong. I should have : nb_month_2014= 0 and nb_month_2015=12.   %let date_start = "01jan2015"d; %let nb_month_2014=0; %let nb_month_2015=0; data _null_; do i=0 to 11 ; dt=intnx("month",&date_start.,i,'same'); if (put(year(dt),z4.)=2014) then %let nb_month_2014 = %eval(&nb_month_2014 + 1) ; if (put(year(dt),z4.)=2015) then %let nb_month_2015 = %eval(&nb_month_2015 + 1) ; end; run; %put nb_month_2014 = &nb_month_2014. ; %put nb_month_2015 = &nb_month_2015. ; Thank you for helping me ! and sorry for my poor english ! Alison   ... View more

Hello everyone, I have a solution to my problem, but I would like something more efficient and quicker.  I think you can understand on the picture what I want.   I would like to associate two sets (blue and green) or more, to do the red one (it's simply to combination of the 2 sets with null data when there is no number).      So this is my script SAS : /* creation of the set where i save the result */ proc sql ; create table lib.montant_result as ( select id_dossier, 0 as montant_1, 0 as montant_2,0 as montant_3, 0 as montant_4, 0 as montant_5, 0 as montant_6,0 as montant_7, 0 as montant_8, 0 as montant_9 from lib.clients ; quit ; %Macro evolution_montant(YYMM) ; /* creation of the blue and green sets */ proc sql ; create table lib.montant_month as ( select id_dossier, case when intck("month",date,"01jul2013") = 1 then montant end as montant_1, case when intck("month",date,"01jul2013") = 2 then montant end as montant_2, case when intck("month",date,"01jul2013") = 3 then montant end as montant_3, case when intck("month",date,"01jul2013") = 4 then montant end as montant_4, case when intck("month",date,"01jul2013") = 5 then montant end as montant_5, case when intck("month",date,"01jul2013") = 6 then montant end as montant_6, case when intck("month",date,"01jul2013") = 7 then montant end as montant_7, case when intck("month",date,"01jul2013") = 8 then montant end as montant_8, case when intck("month",date,"01jul2013") = 9 then montant end as montant_9, FROM lib.clients clients inner join lib.montant&YYMM. mont on mont.id_dossier = clients.id_dossier ); quit ; /*creation and update of the red set (result) */ proc sql ; UPDATE lib.montant_result a SET montant_1 = sum(a.montant_1 ,(SELECT b.montant_1 FROM alison.montant_month b WHERE a.id_dossier = b.id_dossier )), montant_2 = sum(a.montant_2 ,(SELECT b.montant_2 FROM alison.montant_month b WHERE a.id_dossier = b.id_dossier )) , montant_3 = sum(a.montant_3 ,(SELECT b.montant_3 FROM alison.montant_month b WHERE a.id_dossier = b.id_dossier )) , montant_4 = sum(a.montant_4 ,(SELECT b.montant_4 FROM alison.montant_month b WHERE a.id_dossier = b.id_dossier )), montant_5 = sum(a.montant_5 ,(SELECT b.montant_5 FROM alison.montant_month b WHERE a.id_dossier = b.id_dossier )), montant_6 = sum(a.montant_6 ,(SELECT b.montant_6 FROM alison.montant_month b WHERE a.id_dossier = b.id_dossier )), montant_7 = sum(a.montant_7 ,(SELECT b.montant_7 FROM alison.montant_month b WHERE a.id_dossier = b.id_dossier )), montant_8 = sum(a.montant_8 ,(SELECT b.montant_8 FROM alison.montant_month b WHERE a.id_dossier = b.id_dossier )), montant_9 = sum(a.montant_9 ,(SELECT b.montant_9 FROM alison.montant_month b WHERE a.id_dossier = b.id_dossier )) ; quit ; %mend evolution_montant; Thank you in advance. And sorry for my poor english ! Have a good weekend ! ... View more

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 ... View more