I forgot to say that in a lot of scenarios, when you need to create dataset as your final product, my way of creating that tall dataset with the startday and endday columns, perhaps has more advantages, in that this dataset has better traceability, is easier to compare with the raw data, and to validate. ... View more

Hi Paige, thanks a lot for your feedback and valuable comments!   The reasons I said my logic is easier to understand is as follows: 1) @Tom use one single statement to calculate the days for each month, like this: days=min(date2, intnx('month',month,0,'e') )-max(month,date1)+1; I use two data steps (one for months and the other one for days, in my last thread) with many more statements to calculate the days for each month, like this: /*determine start and end day of the 1st month*/ if i=0 then do; startday=date1; endday=intnx('month',date1,0,'end'); end; /*determine start and end day of the last month*/ else if i=intck('month',date1,date2) then do; startday=intnx('month',date2,0); endday=date2; end; /*determine start and end day of other months*/ else do; startday=mdy(month(month),01,year(month)); endday=intnx('month',startday,0,'end'); end; /*calcualte days of each month*/ days=endday-startday+1; The logic of my version is: explicitly show every single step of thinking with one SAS statement or step. And my steps of thinking are shown in the notes in the code above. 2) For me, who is not good at math, writing code this way saves a lot of more complex thinking (and therefore consumes more coding steps), i.e., when you think a basic and single step you explicitly write it out. @Tom combined several thinking steps using one statement which integrates and presents different calculations for the days of the 1st month, the last month, and other months. Maybe what @Tom did is the common way of thinking and common practice for people who have science and math background, but for people who is not that good at math, perhaps my steps is easier to understand and follow. Also, if this code needs to be developed to macros, I think my steps is necessary. Nevertheless, I will learn to combine detailed and basic steps and statements using more advanced and comprehensive thinking and coding techniques later on.   I agree with you that @Tom created a tall dataset at first, and then base on which calculated the days, i.e., creating a tall dataset makes things easier, comparing to @quickbluefish 's solution which at first uses an array and then a loop with advanced functions. So I followed @Tom 's steps to write my version. I have just started to learn array and nested loops and advanced functions and did not follow @quickbluefish 's solution. I will learn those techniques later on. The other reason I followed @Tom 's solution is that the proc sort and proc report steps help me review what I learnt, it is a good example and good practice for me.   By talking about array and (nested) loops I was indeed refer to @quickbluefish 's solution. You are right about this. Thanks again @PaigeMiller for your valuable comments, which offers me ideas on what aspects I need to improve and need to think differently. ... View more

Base on your answer, my version (for creating dataset with days) is like this (the proc sort and proc report steps and the results are the same). Although I used more steps and statements, I think the logic is easier to understand for new users, especially who is not good at math, do not remember many functions, and not get used to more advanced techniques such as array and nested loops. data test; input id iddate date1 date2; informat iddate date: mmddyy.; format iddate date: date9.; datalines; 3 1/1/2024 12/16/2023 4/20/2024 4 1/1/2024 12/31/2023 2/16/2024 5 2/1/2024 1/30/2024 4/15/2024 6 1/1/2024 12/2/2023 2/12/2024 ; run; data want; set test; do i=0 to intck('month',date1,date2); month=intnx('month',date1,i); days=min(date2, intnx('month',month,0,'e') ) -max(month,date1) +1; output; end; format month yymm7.; run; proc print data=want;run; /***my version***/ /*calculate months*/ data mthcalc; set test; do i=0 to intck('month',date1,date2); month=intnx('month',date1,i); output; end; format month yymm7.; run; /*calculate days*/ data daycalc; set mthcalc; /*determine start and end day of the 1st month*/ if i=0 then do; startday=date1; endday=intnx('month',date1,0,'end'); end; /*determine start and end day of the last month*/ else if i=intck('month',date1,date2) then do; startday=intnx('month',date2,0); endday=date2; end; /*determine start and end day of other months*/ else do; startday=mdy(month(month),01,year(month)); endday=intnx('month',startday,0,'end'); end; /*calcualte days of each month*/ days=endday-startday+1; format startday endday date9.; run; proc print data=daycalc;run; /***transpose and report steps***/ proc transpose data=daycalc out=finalrpt (drop=_name_); by id iddate date1 date2; id month; var days; run; proc print data=finalrpt noobs;run; proc report data=daycalc; columns id iddate date1 date2 days,month; define id/group; define iddate/group; define date1/group; define date2/group; define month/across ' '; run; ... View more

Hi Carleigh, thanks a lot for your reply!   What I get from your reply is: for the selected value of &Regionvalue macro variable, there is no change between EstYear1PCT and EstYear3PCT; but for other possible values of &Regionvalue macro variable, maybe there is change from EstYear1PCT to EstYear3PCT, and this is the reason that "No Change" was not a value for the "Forecast" column in the question, but it appears in the answer code. Thanks a lot for your explain!   The reason I posted my thread is: while following the instruction in the question I was a bit confused (because I expected there was a chance of "no change" when comparing two numeric values, also I ignored the instruction that "Unknown" was for null values and did not know which column has missing values in the input dataset), and I wrote my answer like this (see below), I tried twice and did not get it right, then I scrolled to the answer code and see there is a "No Change" value for the Forecast column. I was going to explore the input dataset further but did not want to spend too much time on just one question, so I posted the problem here. Next time when practice this question I will explore the input dataset more. Thanks a lot for your attention and explanation! Best proc sql; select countrycode,indicatorname, estyear1/100 as estyear1pct format=percent7.2, estyear3/100 as estyear3pct format=percent7.2, case when calculated estyear3pct-calculated estyear1pct>0 then 'Increasing' when calculated estyear3pct-calculated estyear1pct<0 then 'decreasing' when calculated estyear3pct-calculated estyear1pct=0 then 'Unknown' end as forecast from sq.globalfindex /*more statement*/; quit; proc sql; select countrycode,indicatorname, estyear1/100 as estyear1pct format=percent7.2, estyear3/100 as estyear3pct format=percent7.2, case when calculated estyear3pct>calculated estyear1pct then 'Increasing' when calculated estyear3pct<calculated estyear1pct then 'decreasing' else 'Unknown' end as forecast from sq.globalfindex /*more statements*/; quit; ... View more

And when just began learn base and sql, most often I create a dataset and then use proc means or proc sql to calculate mean statistics like this: data num; input num; numpct=put(num/100,percent8.2); datalines; 70 60.5 62.87 82 70 ; run; proc print data=num;run; proc means data=num n mean maxdec=2; var num; run; proc sql number; select * from num; select count(num) as n, avg(num)/100 as mean format=percent8.2 from num; quit; ... View more

The materials (for learn SAS as well as the cert. exam preparation) I depend on, refer to, practice many times, and feel most helpful are 1) the "Base SAS 9.4 Procedures Guide" one can find through the SAS Help and Documentation in SAS 9.4 windowing environment and 2) the training materials including pg1, pg2, pg3, macro1, sql1 essentials. Learning these materials is fun and as a learner I really appreciate all this materials 😀😀 ... View more

Hi Stacey! Thanks a lot for your attention and your reply! This is the second time I get feedback from you, I am really so happy about this. I learnt a lot from PG1 and PG2 and PG3, MACRO1 and SQL1 training materials, really good and helpful. All the best! ... View more

Hi Paige, thanks for the suggestion. You suggest that the good way to do the math is a data step, not a macro because a macro here is not necessary and perhaps easy to cause trouble. Below is what I have being doing before, and after I begin learning macro I tried calculate this through a macro because I want to learn macro. data _null_; num=(60+75)/2; format num 5.2; put num=; run; data _null_; num=((60+75)/2)/100; format num percent8.2; put num=; run; ... View more

Thanks a lot, Paige! This is what I tried: %put num=%sysfunc(putn(%eval((60+75)/2),percent7.2)); %let num=%eval((60+75)/2); %put %sysfunc(putn(&num,percent7.2)); %put %sysfunc(putn(&num/100,percent7.2)); %put %sysfunc(putn(&num/100,percent8.2)); The results is as follows (I somehow cannot copy any text in my log so have to use screenshot): Therefore, if I want to use a macro (either use a %let statement or a %let and a %put statement) to calculate the mean of several numbers and get a result in a format of xx.xx% (5 digits including two decimal places, in percent), the best steps (and it looks like the correct percent format is percent8.2, not percent7.2) are 1) use a %let and %eval to calculate the number, and 2) use %sysfunc(putn(&number/100,percent8.2)) in the %put statement to display the number in a percent8.2 format. %let num=%eval((60+75)/2); %put %sysfunc(putn(&num/100,percent8.2)); I have not think the question of store or reuse a macro yet. What I am doing is just use macro to write something and get the result or output (a number, a calculation, a table, or a dataset) according to what I want to produce. As for the above example, the other simpler solution is, if I just want the number without the percent sign % (and this perhaps is the common practice in real scenario, as Paige mentioned in the post and what I myself agree, the % sign and other symbol such as & could cause trouble in macro), I can simply use 1) the %sysevalf() to get the result with up to 14 decimal places or 2) the %sysfunc(putn(),5.2) and %sysfunc(mean(),5.2) in Tom's above post, to get the result in a 5.2 format, like this: %put num=%sysevalf((62.5+76+80.5)/3); %put num3=%sysfunc(putn((62.5+76+80.5)/3,5.2)); %put num4=%sysfunc(mean(70,60.5,62.87,82,70),5.2); ... View more

Thanks a lot, Tom! This is what I tried and get, I think you solved several questions I encountered when struggled with this.   ... View more

Thanks a lot, Paige! this is what I tried out: ... View more

My comments in this post also apply to the practice question (level-challenge) on page 317 of SAS SQL1: Essentials course note PDF, the question and answer code (which I guess I would not use the code if there is any other alternative I can choose) are as follows: And have to say countw() function is not good here because the character string is complex (has several ampersand& and one hyphen-) and struggle many times using %nrstr  to deal with delimiters (and this till now I do not think I can figure out the thorough and detailed usage) was not able to fix the errors (see below). The best solution perhaps is the select into :list1- and %do i=1 %to &sqlobs one.   /* %put &regionvalue; */ /* */ /* %let n=%sysfunc(countw(a&b,c&d,e&f,g-h)); */ /* %put &n; */ /* */ /* %let n=%sysfunc(countw(&regionvalue)); */ /* %put &n; */ /* */ /* %let string=%nrstr(&regionvalue); */ /* %put &string; */ /* %let i=%sysfunc(countw(&string,',')); */ /* %put &i; */ %macro split(tab,col); proc sql; select distinct &col into :colvalue1- from &tab where &col^=' '; quit; %do i=1 %to &sqlobs; proc sql; select * from &tab where &col="&&colvalue&i"; quit; %end; %mend split; %split(sq.globalmetadata,region); ... View more

PS: the countw() function and the code I took as reference is on page37, page41 of SAS Programming3: Advanced Techniques course notes PDF. ... View more