About s_lassen

s_lassen

You can do it like this (data must be sorted by ID, like your sample data): data want; merge a(keep=id flag where=(flag>0) in=ok) a; by id; if ok; run;

s_lassen

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];

s_lassen

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.

s_lassen

What happens is that the conversion numeric to character is detected during the compilation of the data step, so you get that message even if the statement is never executed. The "invalid argument to input" problem is detected during the execution of the data step, so if the line is not executed, you will not get that message. The variables C and D will be created in the compilation step, but nothing will be assigned to them if the assignment statements are inside the "if 0 then do" block (D will be missing in both cases, as "1" is not a string in DATE9 format). If you want to avoid the messages in the log, then do not use numeric inputs to character functions if you have a variable that may or may not be valid input for the INPUT function, and you do not want messages when the input is not valid, you can use the "??" modifier, e.g. "D=input(<character variable>,?? date9.)"

s_lassen

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.

s_lassen

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.

s_lassen

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.

s_lassen

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;

s_lassen · ‎09-05-2024

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.

s_lassen · ‎07-31-2024

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.

s_lassen · ‎07-26-2024

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.

s_lassen · ‎07-19-2024

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)?

s_lassen · ‎06-25-2024

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.

s_lassen · ‎06-23-2024

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.

s_lassen · ‎06-23-2024

This error is probably not related to SAS or even to SQL server, it is probably in the HTTP setup - the may be that a request is made in HTTP, but the server expects a HTTPS query. What does your libname statement for ODBC look like?

Online Status	Offline
Date Last Visited	yesterday

Re: how to print all obs for an ID that has a flag value > 0 in any of...

Re: Array- PCT change from month to next month

Re: Add in additional rows to fill missing data

Re: How exactly dose SAS process if/do/end in a data step?

Re: How to generate dataset with macro function and looping?

Re: SAS installation is stuck

Re: SAS installation is stuck

Re: Convert Text Date in a Proc SQL Where

Re: How to check if data set is sorted

Re: PUTN error. What's the problem with the conversion to 6 characters...

Re: Counting decimals in numeric fields.

Re: special characters

Bug in proc sql and fcmp variable scope (SAS 9.4M8)

Re: nth max value using proc sql and datastep

Re: Please explain me this macro below bolded line

Re: how to print all obs for an ID that has a flag value > 0 in any of...

Re: Array- PCT change from month to next month

Re: How exactly dose SAS process if/do/end in a data step?

Re: How to generate dataset with macro function and looping?

Re: Extracting line from free text that contains a keyword

Re: how to print all obs for an ID that has a flag value > 0 in any of...

Re: Array- PCT change from month to next month

Re: Add in additional rows to fill missing data

Re: How exactly dose SAS process if/do/end in a data step?

Re: How to generate dataset with macro function and looping?

Re: SAS installation is stuck

Re: SAS installation is stuck

Re: Convert Text Date in a Proc SQL Where

Re: How to check if data set is sorted

Re: PUTN error. What's the problem with the conversion to 6 characters...

Re: Converting the CTE recursive query to SAS logic

Re: Join two datasets with different structure (similar to vlookup)

Re: How can I create a variable indicating which values are the same?

Re: Count the weeks

Re: SAS EG Query Getting Below ERROR: CLI cursor extended fetch error