I assume that what you are trying to do is actually what this code does (no macro stuff): data TEST1; infile datalines; input ID $ MOD $; length PRODUCT $ 10; if MOD = 'N' then M1 = 'N'; else if MOD in (6,8,9) then M1 = 'Y'; else M1 = 'N'; if ID = "0001" and M1 = "Y" then PRODUCT = "SCOOTER"; else if ID = "0001" then PRODUCT = "BIKE"; else if ID = "0002" then PRODUCT = "SKATEBOARD"; datalines; 0001 N 0001 0 0001 6 0002 8 ; run; I assume that you turned the stuff into a macro because you want to be able to call the code with different input variables. The problem you have comes from the fact that the macro code is executed BEFORE the actual datastep (the macro is simply a code generator which creates code for the data step, which is then compiled).   To get an idea of what happens, you can use the MPRINT option: 78 data TEST1; 79 infile datalines; 80 input ID $ MOD $; 81 length PRODUCT $ 10; 82 83 %PRODUCT(ID,MOD); MPRINT(PRODUCT): if ID = "0001" and "N" = "Y" then PRODUCT = "SCOOTER"; MPRINT(PRODUCT): else if ID = "0001" then PRODUCT = "BIKE"; MPRINT(PRODUCT): else if ID = "0002" then PRODUCT = "SKATEBOARD"; 84 datalines; What happens is that the macro variable MOD in the macro is interpreted during macro execution(with the value "MOD" in the call shown, not the value of the datastep variable), not during datastep execution. The local macro variable  M1 is thus always set to N.    What you may try is something like this %MACRO PRODUCT(ID, MOD) ; if &ID = "0001" and &mod in(6,8,9) then PRODUCT = "SCOOTER"; else if &ID = "0001" then PRODUCT = "BIKE"; else if &ID = "0002" then PRODUCT = "SKATEBOARD"; %MEND; options mprint; data TEST1; infile datalines; input ID $ MOD $; length PRODUCT $ 10; %PRODUCT(ID,MOD); datalines; 0001 N 0001 0 0001 6 0002 8 ; run; ... View more

I think the problem comes from the fact that you define the arrays "backwards" in time, from 12 to 1 instead of from 1 to 12.    Take the first assignment in the array (I=1): PCT_change[i]=(AAA[i]-AAA[i+1])/AAA[i+1]; resolves to  PCT_change[1]=(AAA[1]-AAA[2])/AAA[2]; which again resolves to PCT_change_12_To_11=(rev12-rev11)/rev11; But what you want is PCT_change_12_To_11=(rev11-rev12)/rev12; which should be written as  PCT_change[i]=(AAA[i+1]-AAA[i])/AAA[i]; and you should also change the IF condition: if AAA[i] not in (.,0) then PCT_change[i]=(AAA[i+1]-AAA[i])/AAA[i]; ... View more

Here is code that fills out all missing months in the years in the data (data must be sorted as in the BY statement): data want; set temp; by col1 col2 col3 year; _prev_month=lag(month); _value=value; value=0; if first.year then do month=1 to month-1; output; end; else do month=_prev_month+1 to month-1; output; end; value=_value; output; if last.year; value=0; do month=month+1 to 12; output; end; drop _:; run; This is maybe not the most elegant solution, but it may execute faster than the other solutions suggested. ... View more

One possibility may be to create a table containing all the store IDs and months that you want, and then merge with the original: data all; set have(keep=store); by store; if first.store; do month='2023-01','2023-02','2023-03'; output; end; run; data want; merge have(in=ok) all; by store month; if not ok then sales=-999; run; I do not think there is any reason to use macros for this. ... View more

Yes, the zip file may be where you should get the XML file. It should be possible to just extract the one XML file from the zip file, and not all the 15 GB. On the other hand, if something went wrong in the first extraction, it may be wiser to re-extract the whole shebang once more, as there may be errors in other files as well.  ... View more

The problem may be that the XML file you had was somehow corrupted (probably truncated), which may be why you got the first error. When you delete the XML file, SAS installation hangs as it cannot find the file.    I think what you need to do is to stop the installation process that hangs, download the XML file again (hopefully without errors this time), and then restart the installation. ... View more

If I understand your second post correctly, what you have is not a SAS DATE numeric variable (as shown in your first post), but a text variable in the format "mm/dd/yyyy". If that is the case, you will have to convert the character values to SAS dates in the actual WHERE clause, something like %let CheckDate = '01FEB2002'd; Proc sql; select * from Have where input(DOB,mmddyy10.) > &CheckDate ;quit; ... View more

You have already had quite a lot of advice. However, there is one other reason checking with a data step may be the faster way - say we have a lot of variables, but only want to check sorting on a few of them, then you can use KEEP= to minimize the data that is actually read.   May save quite a lot of memory, and possibly also some time, e.g.: %let sorted=NO; data _null_; do until(eof); set sashelp.class(keep=age) end=eof; by age; end; call symputx('SORTED','YES'); run; %put &=sorted; I put the DO UNTIL in because it saves the code to reset the variables to missing before reading each observation. ... View more

The error message you are getting is simply that the second argument should be a character string with the format you want to use. Like this: 72 data _null_; 73 a=putn(%substr(&_CYCLE.,1,6), '8.'); 74 put a $quote.; 75 run; " 201808" This produces a right-aligned string of length 8. If that is what you want, I would suggest using WHERE CLOSINGCYCLE = " %substr(&_CYCLE.,1,6)"; which is a much simpler way to get the right-aligned string.     ... View more

It looks like your code can be converted to SAS data step code like this:   1. Get the FULL_DATE from TBL: Proc sql noprint; SELECT MAX(full_date) into :full_date from tbl; quit; 2. Process the data: data want; set final; by id rownum; if rownum=1 then do; f_dose=fill_dt; l_dose=min(fill_dt+fill_days_supply-1,&full_date); span=1; end; else do; f_dose=min(max(fill_dt,l_dose+1),&full_date+1); if fill_dt-l_dose>=60 then span+1; l_dose=min(max(fill_dt,l_dose+1)+fill_days_supply-1,&full_date); end; retain l_dose span; format l_dose f_dose date9.; run; The code shown assumes that the tables exist in SAS.WORK, and that the dates are SAS date values. The FINAL table should be sorted by ID and ROWNUM. The code also assumes that rows exist for consecutive ROWNUM values (1,2,3...) for all IDs. ... View more

Assuming the table B is already sorted by VAR2, I would use a solution like this: proc sort data=a; by var2; run; data want; merge a b; by var2; array var1_ (*) var1_1-var1_4; type=var1_(var1); drop var1_:; run; You may then want to sort back by ID and VAR1, to get data in the same order as your example.   But I do not understand the second observation in your WANT example: should the TYPE variable not be "b3" here (VAR1_3 from the row in B with VAR2=2)? ... View more

Assuming data like this (I added the ROW_ID variable to identify rows): data have; row_id=_N_; input ID_A ID_B ID_C ID_D; cards; 1 1 1 1 2 2 2 3 3 4 4 4 1 1 3 5 2 2 6 6 1 2 3 4 ;run; I would first convert to a long format and sort: proc transpose data=have out=long; var ID_:; by row_id; run; proc sort data=long; by row_id col1; run;   Then it is easy to find the matches: data match; do until(last.col1); set long; by row_id col1; length match $10; call catx(',',match,substr(_name_,4)); end; if index(match,','); /* not single values */ keep row_id match; run; And finally merge with the original data: data want; merge have match; by row_id; run; The answer to @Kurt_Bremser 's question is in this case that if there are two matching pairs, we will create an output row for each. ... View more

If you are calling the WEEKNUM function in Excel without any "Return type" (the second parameter), the result should be like the WEEK function in SAS with the 'U' modifier, except that the first week in Excel always has number 1. So, if the first day of the year is a Sunday, the two calls should return the same values for that year, but if it is not, the Excel function will return a value that is one greater than the SAS function.   You should be able to get the same result as in Excel like this: week_no=week(date,'U')+(weekday(mdy(1,1,year(date)))>1); - assuming your date variable is called DATE.   If you implement this code, I would recommend putting in a comment (you can steal the text I wrote above, be my guest), as the code by itself is pretty cryptic. ... View more