About Tom

Tom

Try something like this: data _temp; do grp=1 to 4; do i=1 to 100; x=sin(i/10.0); output; end; end; run; %let ds=_temp; %let var=x; %let byvar=grp; %let long=0.1; %let avgback=3; data step1; retain alpha %sysevalf((2.0/(&long.+1))) ; do _ind_=1 by 1 until(last.&byvar); set &ds; by &byvar.; where not missing(&var); retain _initial_&var; array _lag [0:%eval(&avgback-1)] ; if first.&byvar. then _initial_&var=&var; if _ind_<=&avgback then ema_fs_&var=&var; else ema_fs_&var = alpha*&var + (1-alpha)*mean(of _lag[*]); output; _lag[mod(_ind_,&avgback)]=&var; end; *drop alpha _lag: ; run; title "ema_fs_&var lag &avgback outcome"; ods layout gridded columns=2 rows=2 advance=table; ods graphics /width=480px height=300px; proc sgplot data=step1; by grp; series x=_ind_ y=&var/ lineattrs=( color=blue thickness=2 pattern=solid); series x=_ind_ y=ema_fs_&var/ y2axis lineattrs=( color=red thickness=2 pattern=solid); run;quit; ods layout end; The use of permanent variables for the ARRAY and placing the SET inside the DO loop means that the arrayed variables are automatically reset to missing at the start each BY group. Using an array that is indexed from 0 to N-1 instead of from 1 to N makes it easier to use the MOD() function to indicate which index in the array needs to be replaced with the current value. Note: I don't think you want to exclude the missing values. That would mess up the _IND_ variable you later use as your X axis.

Tom

Turn on MPRINT option and you can see you are making a common mistake with your use of LAG() functions. You are running them conditionally. In particular this block of code. else do; &var._fs&avgback._long= (&alpha1.)*&var.+(1-&alpha1.)* (mean(&avgbackline_long.)); end; The macro varaible AvgBackLine_Long appears to have been constructed as LAG1(..),LAG2(..),....LAGn(..). For the LAG() function to know what the previous value was you have to pass it the previous value. By skipping the call to the LAG() function sometimes those values are not pushed onto the stack and so cannot be popped back off it later. You might be able to fix your code by just changing that IF/THEN/ELSE block into something like this: &var._fs&avgback._long= (&alpha1.)*&var.+(1-&alpha1.)* (mean(&avgbackline_long.)); if _ind_<=&avgback. then &var._fs&avgback._long= &var.; But there is probably a much simpler way to code your algorithm using arrays.

Tom

You need to state what GUI interface you are using that is captured in your picture. One of the responses is guessing it is Enterprise Guide.

Tom

You could also just generate two datasets and then merge them together. Create a unique id to perform the merge with, let's call it ID. To make the named input happy define variables for all of the named fields on the JOB= lines. data main jobs(drop=name lastrun); infile cards truncover ; input @; if _infile_ ^=: 'JOB=' then do; id+1; n=0; input name $ 1-8 lastrun $ 63-66; output main; end; else do; n+1; length job $8 schid $3 vrsn $10 leadtm $2; input (job--leadtm) (=) ; output jobs; end; cards; FREDFRED 004 FREDFRED FMS 001 ALL *NONE* YES 000 000 1732 BARNEY 004 BARNEY ABZ 001 ALL *NONE* YES 000 000 1341 JOB=JOB00001 SCHID=000 VRSN=09063/1602 LEADTM=08 JOB=JOB00043 SCHID=000 VRSN=94322/1613 WILMA 004 WILMA TRE 001 ALL *NONE* YES 000 000 0000 JOB=STEP9999 SCHID=000 VRSN=07263/0650 LEADTM=99 BETTY 004 BETTY KIL 001 ALL *NONE* YES 000 000 2351 ; data want; merge main jobs; by id; run; Result

Tom

It would be easier to read from a file, since you can use the END= option of the INFILE statement. So let's convert your example lines into a file first so we have something to program against. options parmcards=text; filename text temp; parmcards; FREDFRED 004 FREDFRED FMS 001 ALL *NONE* YES 000 000 1732 BARNEY 004 BARNEY ABZ 001 ALL *NONE* YES 000 000 1341 JOB=JOB00001 SCHID=000 VRSN=09063/1602 LEADTM=08 JOB=JOB00043 SCHID=000 VRSN=94322/1613 WILMA 004 WILMA TRE 001 ALL *NONE* YES 000 000 0000 JOB=STEP9999 SCHID=000 VRSN=07263/0650 LEADTM=99 BETTY 004 BETTY KIL 001 ALL *NONE* YES 000 000 2351 ; Now you can read ahead using trailing @@ on the INPUT statement. Keep track of how many JOB= lines you have seen so you know whether you need to execute and extra OUTPUT statement. You probably do NOT want the behavior of the MISSOVER option, instead use TRUNCOVER so it doesn't ignore short values at the end of lines. And to read those JOB= lines you could use @ 'string' pointer control. data have; infile text truncover end=eof; input name $ 1-8 lastrun $ 63-66; do n=0 by 1 while(not eof); input @@; if _infile_ =: 'JOB=' then do; if n then output; input @'JOB=' rqmt :$8. @'LEADTM=' leadtm :$2. ; end; else leave; end; output; run; Or if you make the variable names match the strings in the lines you can used NAMED input style. data have; infile text truncover end=eof; input name $ 1-8 lastrun $ 63-66; do n=0 by 1 while(not eof); input @@; if _infile_ =: 'JOB=' then do; if n then output; input job= :$8. leadtm= :$2. ; end; else leave; end; output; run; Results Notice that using the named input caused the LEADTM value to be remembered for the second JOB= line. To fix the issue that named input does not force variables to missing when not found you could add code to clear the value before the input. But make sure to set the variable's type and length before hand. data have; infile text truncover end=eof; input name $ 1-8 lastrun $ 63-66; do n=0 by 1 while(not eof); input @@; if _infile_ =: 'JOB=' then do; if n then output; length job $8 leadtm $2 ; call missing(job,leadtm); input job= leadtm= ; end; else leave; end; output; run;

Tom

Your math is off. Since the length of the macro variable is longer than the character variable you created in the data step you are seeing truncation. 98 %put %length(&allv_Y); 3171 The maximum length of a character variable is 32K bytes. The maximum length of a macro variable is 64K bytes. Note that the string will be longer once you add those extra characters. If you expect to do this for lists that approach the limit of a character variable you might want to switch to a pure macro code solution. If you expect to need to do this for a list that could exceed the length of macro variable you might want to switch to a solution that keeps the list as multiple observations in a dataset instead and then use a data step to write the generated code to a file. One way around the macro variable limit is define an actual MACRO instead. For example if you have a dataset names HAVE with a character variable named VAR with values like: data have; input var :$32. @@ ; cards; LAKOACH AB_PA_IND_bad_ANYBAD AB_PA_MKR_TASHLUM AB_PA_AGE_VETEK_MASHK AB_PA_Y_TAS_SUMLLL_TYPE AB_PA_BITCHONOT AB_NEWC_Y_NIZHOS AB_NEWC_Y_NIZMISGOSH_HARIGA AB_NEWC_AKM_HZR AB_NEWC_MASHKANTA AB_NEWC_INC_COMPONENT AB_NEWC_INC_TREND ; You could run a data step like this to create code that would define a macro name VARLIST. filename code temp; data _null_; set have end=eof; file code; if _n_=1 then put '%macro varlist;' ; put var @; if eof then put / '%mend;'; run; %include code / source2; That you could then call where you need that list of names to appear. data new; length %varlist 8 ; call missing(of _all_); run;

Tom

Just remember the current setting so you can change it back. %let optsave=%sysfunc(getoption(dkicond)); options dkricond=NOwarning; data cars; set sashelp.cars(KEEP=model make invoice color); run; option dkricond=&optsave;

Tom

@odahviing wrote: PUTC and INPUTN indeed work smoothly. However, this just get a new question to me: why format "YYMMDD10." resolves to "$YYMMDD10." when execute PUT in compile phase? Could you please explain this question ? SAS is trying to be helpful (and also trying to allow old programs to be run with new versions of SAS). Since you used a CHARACTER variable with the PUT() function you needed to specify a character format. In the old (very very old) days SAS did not require that character formats start with a $. When SAS could not find find a character format named YYMMDD it decided you must have meant $YYMMDD. But it could not find that one either so it generated an error message use the "fixed" format name.

Tom

The SAS log for your example explains what is happening. 71 data test; 72 x=0; 73 if x=1 then do; 74 chk=5/0; NOTE: Division by zero detected during the compilation phase, detected at line 74 column 8. 75 end; 76 run; Because 5 and 0 are constants the compiler can check if they will cause trouble before the step starts running. If you change it to divide by X instead then the compiler will not issue that warning since it cannot know what value X will have. Even in this case where X has a constant value. 71 data test; 72 x=0; 73 if x=1 then do; 74 chk=5/x; 75 end; 76 run; NOTE: The data set WORK.TEST has 1 observations and 2 variables.

Tom

If you don't want the data step compiler to complain about your usage of the wrong type of FORMAT in the PUT() function call then switch to using PUTN() instead so that the checking is done at execution time instead. Similarly use INPUTN() in your other branch. This works because PUTN() and INPUTN() (and corresponding character versions) take a character expression as the second argument instead of a format literal. So the compiler cannot check it since the value is not known until the step runs. %macro get_num_char(dst,v1,f1) ; data nc_&dst.; set &dst.; length &v1._num 8.; length &v1._char $20.; &v1._type = vtype(&v1.); if &v1._type='N' then do; &v1._num=&v1.; &v1._char=strip(putn(&v1.,"&f1.")); end; if &v1._type='C' then do; &v1._char=&v1.; &v1._num=inputn(&v1.,"&f1."); end; run; %mend get_num_char; Note: You should also replace STRIP() with LEFT() since you don't really need to remove the trailing spaces since they will just be added back when the value is written into the fixed length character variable.

Tom

Good topic, poor choice of vocabulary. SAS definitely excludes missing values from "analytics". That is one of the reasons it became the gold standard for data analysis. What you are talking about is how those databases handle COMPARISON operators. Such as testing if two values are equal or if one is larger than the other. That is the place where SAS handles things differently. The difference is that SAS boolean operators are truly boolean, there are only two possible results. But in the other systems you are talking about they use tri-level logic. Boolean operators can return three possible results TRUE, FALSE and NULL. To accomplish this SAS treats all missing values as less than any actual number. And treats the same missing value as being equal. For example two values that both have the default missing value are equal. But if one has special missing value of .A then it is different.

Tom

Your first program does what it asked it to do. data have; input var1 $ var2 $ WANT ; cards; ROOMA DESK01A 1 ROOMA DESK01B 1 ROOMA DESK05B 5 ROOMA DESK06A 6 ROOMB DESK09A 9 ROOMB DESK09B 9 ROOMC DESK01B 1 ROOMC DESK05A 5 ROOMD DESK10B 10 ROOME DESK15A 15 ROOME DESK15B 15 ROOME DESK16 16 ; %let range_1 = 13; %let range_2 = 24; data test_1; set have; if compress(var2,'', 'A') >= &range_1. and compress(var2,'', 'A') <= &range_2. then test = 'YEAH'; run; proc print; run; Results Obs var1 var2 WANT test 1 ROOMA DESK01A 1 2 ROOMA DESK01B 1 3 ROOMA DESK05B 5 4 ROOMA DESK06A 6 5 ROOMB DESK09A 9 6 ROOMB DESK09B 9 7 ROOMC DESK01B 1 8 ROOMC DESK05A 5 9 ROOMD DESK10B 10 10 ROOME DESK15A 15 YEAH 11 ROOME DESK15B 15 YEAH 12 ROOME DESK16 16 YEAH But why does it work? Because SAS helpfully automatically converted the strings like '1' and '15' that your COMPRESS() function call generated into numbers: 90 data test_1; 91 set have; 92 if compress(var2,'', 'A') >= &range_1. 93 and compress(var2,'', 'A') <= &range_2. 94 then test = 'YEAH'; 95 run; NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 92:6 93:7 If you want to convert the value to numbers yourself then use the INPUT() function. data test_2; set have; test2 = input(compress(var2,' ','A'),32.); check= (test2 = want); run; Result Obs var1 var2 WANT test2 check 1 ROOMA DESK01A 1 1 1 2 ROOMA DESK01B 1 1 1 3 ROOMA DESK05B 5 5 1 4 ROOMA DESK06A 6 6 1 5 ROOMB DESK09A 9 9 1 6 ROOMB DESK09B 9 9 1 7 ROOMC DESK01B 1 1 1 8 ROOMC DESK05A 5 5 1 9 ROOMD DESK10B 10 10 1 10 ROOME DESK15A 15 15 1 11 ROOME DESK15B 15 15 1 12 ROOME DESK16 16 16 1 Two things to note. 1) I explicitly asked COMPRESS() to remove spaces as well has letters. You might also instead try tell it to only keep digits. 2) I used the 32. informat to read the generated string. The INPUT() function does not care if the width of the informat being used is larger than the length of the string being read. And since 32 is the maximum width that the numeric informat supports it is best to use that when you don't know how long the string being read might be.

Tom · ‎12-02-2025

The macro processor operates on the TEXT of your program first and then passes the result to SAS to execute. SInce the %LET is BEFORE the RUN statement it executes BEFORE the data step executes. So you are running this program: %let fname=pilote_????_????_validation_v001.*.spds9; %let path2=/dwh_operation/.../pilot/&fname.; filename oscmd pipe "ls &path2. -t | head -n1 2>&1"; %let cyear=%sysfunc(substr(&a.,65,4)); data _null_; infile oscmd; input; a= _infile_; call symputx("a", a); run; %put &=cyear; Why not just ask the SAS code to make the macro variable? data _null_; infile oscmd; input; a= _infile_; call symputx('a', a); call symputx('cyear',substr(a,65,4)); run; %put &=a; %put &=cyear; I have no idea why you think the year is always going to be in columns 65 to 68 , but if it is then why not just run this code instead? That way the code more closely matches the intent. data _null_; infile oscmd truncover; input cyear $ 65-68; call symputx('cyear',cyear); run; %put &=cyear;

Tom · ‎12-01-2025

If you know the maximum number of DEGEMs or AMTs per OFFERID then it is simple using ARRAYs. data have; input offerID degem amnt; cards; 111123 200 1000 111123 300 2000 111123 400 3000 ; data want; set have; array _degem degem1-degem3 ; array _amnt amnt1-amnt3; do _n_=1 by 1 until(last.offerid); set have; by offerid; _degem[_n_]=degem; _amnt[_n_]=amnt; end; drop degem amnt; run; If you don't know the number you have two choices. Just pick something that you know is too large. Or calculate it. For example you could use PROC SQL to create a macro variable. proc sql noprint; select max(count) into :max_count trimmed from (select offerid,count(*) as count from have group by offerid) ; quit; Then use that macro variable to generate the arrays' upper bounds. array _degem degem1-degem&max_count ; array _amnt amnt1-amnt&max_count ;

Tom · ‎12-01-2025

If END_DATE is actually a DATE value then the reason you get 2 is because the actual number that SAS uses to represent the 4th day in October of 2014 is 20,000. Since you asked SAS to convert that into a string so it could be used as the argument to the SUBSTR() function it used the BEST12. format so the result was 7 spaces followed by the 5 digit string 20000. You then picked only the 7 spaces and the '2'. And then forced SAS to convert that string into the number 2 which you multiplied by 1. If END_DATE is instead a CHARACTER variabel it would also need to have those some 7 leading spaces for the result to be 2. Example: 71 data _null_; 72 end_date= '04OCT2014'd; 73 Date1=SUBSTR(End_Date,1,8)*1; 74 put (_all_) (=/); 75 run; NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 73:16 NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 73:9 end_date=20000 Date1=2 NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 76 77 data _null_; 78 end_date= ' 2014-10-04'; 79 Date1=SUBSTR(End_Date,1,8)*1; 80 put (_all_) (=/); 81 run; NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 79:9 end_date=2014-10-04 Date1=2 NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds If you want to convert a STRING like '2014-10-04' into a DATE then using the INPUT() function and the YYMMDD informat. Make sure tor remove the leading spaces. actual_date = input(left(end_date),yymmdd10.); If you want the value of ACTUAL_DATE to print in the style YYYYMMDD then attach the YYMMDDN format to it. format actual_date yymmddn8.; If you want to make the variable DATE1 be a NUMBER in the style YY,YYM,MDD then use the PUT() function with YYMMDDN8. format to convert the date into a string so you can then use the INPUT() function with the normal numeric informat of 8. to convert that string back into a number. date1 = input(put(actual_date,yymmddn8.),8.);

Online Status	Offline
Date Last Visited	yesterday