About gamotte

gamotte · ‎02-28-2019

' ' is the missing value for charcater variables while . is the missing value of numerics. You cannot convert a numeric variable to character on the fly. If you don't want the . to appear in reports you can use option missing=' ';

gamotte · ‎02-28-2019

Hello, I think there is a proc for this kind of things but i don't remember the name. Here is a solution with a recursive macro. An additional stop criterion should be added to avoid infinite loops if they are likely to occur. data id_lists; input prev_id $ next_id $; datalines; a b w x b c x y c d ; run; proc sql noprint; CREATE TABLE steps AS SELECT DISTINCT step0 FROM ( SELECT prev_id AS step0 FROM id_lists UNION SELECT next_id AS step0 FROM id_lists ); quit; %macro recursive_join(iter=0); %local cont iter1; %let iter1=%eval(&iter.+1); proc sql noprint; CREATE TABLE steps AS SELECT i.prev_id AS step&iter1., s.* FROM steps s LEFT JOIN id_lists i ON i.next_id=s.step&iter.; SELECT sum(step&iter1. ne " ")>0 INTO :cont FROM steps; quit; %if &cont. %then %do; %recursive_join(iter=%eval(&iter1.)); %end; %mend; %recursive_join; data want; set steps; array s step:; rename step0=id_sequence; length sequence_origin $8.; sequence_origin=coalescec(of s(*)); keep sequence_origin step0; run;

gamotte · ‎02-20-2019

Hello, You can try something like that %macro prep(dsname, source, race, sex, categ, pop); data &dsname; set CEN_SUM_&source.(rename=(&pop.=pop)); sex=&sex.; raceth=&race.; agecat=&categ.; run; %mend; data _NULL_; sexnum=0; i=0; do sex="M", "F"; sexnum+1; racenum=0; do race="NWH", "ASIAN"; racenum+1; categnum=0; do categ="0_25", "25_35", "35_45", "45_55", "55_65", "65_75", "75"; i+1; categnum+1; call execute(cats('%prep(pop',i,',',race,',',racenum,',',sexnum,',',categnum,',',sex,categ,');')); end; end; end; run;

gamotte · ‎02-20-2019

hello, Is &M1. the same macrovariable as &day. ? Try where bday >"&M1."d; Do you have to use macros ? can't you just write where bday > intnx("month", today(),2);

gamotte · ‎02-20-2019

Hello, Do you mean something like this ? data have; infile datalines dlm=":" dsd; length group $1. ddd $ 50.; input group ddd; datalines; A: 29 32 26 15 34 15 23 B: 11 30 34 13 27 24 21 C: 22 12 20 7 3 27 4 D: 16 21 13 6 0 9 25 E: 6 2 10 -3 8 1 -4 ; run; If so, you are not doing the future you a favor by using a unique variable to store several values as it makes data manipulation more difficult.

gamotte · ‎02-19-2019

Hello, You can use arrays : data have; input A_1 B_1 C_1 D_1; cards; 1 2 3 4 5 6 7 8 ; run; proc sql noprint; SELECT NAME, cats('Log',substr(NAME,1,1)) INTO :vars SEPARATED BY ' ', :logs SEPARATED BY ' ' FROM dictionary.columns WHERE LIBNAME="WORK" AND MEMNAME="HAVE"; quit; data want; set have; array A &vars.; array B &logs.; do over A; B=log(A); end; run; Edit : I didn't use the same variables names as in your question => added a request on dictionary columns to generate the arrays definitions.

gamotte · ‎02-18-2019

Hello, Here is a solution where the code is generated by call execute's. It uses a loop to retrieve the names of the dataset's columns with call vnext. Whenever the name contains "loading_time" it generates a line of code that computes the corresponding difference. Note the "stop" at the end of the data step that prevents it to read further than the first observation. data have; input login_loading_time check_closing_time login_closing_time check_loading_time; cards; 400 415 456 421 405 214 413 411 410 412 400 403 450 423 451 401 454 411 441 421 458 401 433 411 750 410 412 410 760 710 451 421 770 798 413 433 ; run; data _NULL_; set have; length NAME $32.; call execute('data want; set have;'); do while (NAME ne "NAME"); call vnext(NAME); if find(upcase(NAME),"LOADING_TIME") then do; basename=scan(NAME,1,'_'); call execute(cats(basename,'_diff=sum(',basename,'_loading_time,-',basename,'_closing_time);')); call execute('keep '||strip(basename)||'_diff;'); end; end; call execute('run;'); stop; run;

gamotte · ‎02-12-2019

Hello, Another solution using an array and coalescec function to retrieve the first non missing element : data test; infile datalines dlm=' ' dsd; input id $ V1 $ V2 $ V3 $; length final $1.; array v(*) v3-v1; final=coalescec(of v(*)); datalines; 1 x y z 2 x y 3 x 1 x y 3 x y z ; run;

gamotte · ‎01-29-2019

Hello, You seem to mix macrovariables an datasets colums. See the following example %macro tt(variable=); data test; set sashelp.class; reg=resolve(cats('%sysevalf(',catx('/',&variable),')')); run; %mend; %tt(variable=%bquote(age,weight)); with %bquote(age,weight) as the macro parameter : catx('/',&variable) resolves to catx('/', age, weight) creates a string such as "13/84" (Alice's data in sashelp.class) cats('%sysevalf(',catx('/',&variable),')') then creates the string '%sysevalf(13/84)' Then the resolve function is used to evaluate the string as a macro command. In your initial program, you had the instruction reg=%sysevalf(catx('/',&variable)); catx('/',&variable) creates the same string as above %sysevalf being a macro function is executed first and will raise en error since its argument is not an arithmetical expression that can be evaluated. @Quentin's solution %sysfunc(catx(/,%unquote(&variable))); takes the catx function and creates its macro equivalent, so it will generate the code : reg=age/weight which will then be executed by the data step.

gamotte · ‎01-04-2019

If the time span does not exceed a hundred years, this can be dealt with using YEARCUTOFF option. http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000197881.htm

gamotte · ‎01-04-2019

As explained in @PeterClemmensen's link the DATESTYLE option can be used to handle such ambiguities %macro inputDate(ds); option DATESTYLE="&ds."; data have_&ds.; format dd date9.; dd=input("121110",anydtdte.); run; %mend; %inputDate(MDY); %inputDate(DMY); %inputDate(YMD);

gamotte · ‎01-04-2019

Hello, You can use the anydtdte informat : data want; keep dd; format dd date9.; input dd anydtdte.; cards; 20180118 18jan2018 011818 ; run;

gamotte · ‎01-03-2019

Hello, Do you want to use a reference dataset to generate macro calls ? If so, here is a way to achieve this : %macro comp(ds=, id=); %put &=ds. &=id.; %mend; data meta; input ds_name $ var_id $; cards; foo x bar y baz z ; run; data _NULL_; set meta; call execute(cats('%comp(ds=',ds_name,', id=',var_id,');')); run;

gamotte · ‎01-03-2019

Please post the log as text as some contributors cannot or won't open attached documents. It would be useful to have the complete log indicating which code has been executed. For instance, here is the log i obtain : 696 697 data _NULL_; 698 set work.mpart; 699 array fixed _NUMERIC_; 700 701 call execute('data want; set work.mpart;'); 702 703 do over fixed; 704 if vformat(fixed)=:"DATETIME" then do; 705 varname=vname(fixed); 706 call execute(cat('drop ',strip(varname),';')); 707 call execute(cat('format N_',strip(varname),' date9.;')); 708 call execute(cats('N_',varname,'=datepart(',varname,');')); 709 end; 710 end; 711 712 call execute('run;'); 713 714 stop; 715 run; NOTE: There were 1 observations read from the data set WORK.MPART. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: CALL EXECUTE generated line. 1 + data want; set work.mpart; 2 + drop BDAY; 3 + format N_BDAY date9.; 4 + N_BDAY=datepart(BDAY); 5 + drop REcDate; 6 + format N_REcDate date9.; 7 + N_REcDate=datepart(REcDate); 8 + drop ReTDate; 9 + format N_ReTDate date9.; 10 + N_ReTDate=datepart(ReTDate); 11 + run; NOTE: There were 1 observations read from the data set WORK.MPART. NOTE: The data set WORK.WANT has 1 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds

gamotte · ‎01-03-2019

From the error message, i would say that you forgot the semi-colon after 'set work.mpart' in the first call execute. If not, post the complet log to help us diagnose the problem.

Online Status	Offline
Date Last Visited	‎08-13-2025 08:30 AM

Re: Stream a binary file

Stream a binary file

Re: Syntax error with parenthesis

Re: if-else if-else with do

Re: Macro Do Loop

Re: Top N values in sas

Re: How to add months in macro variable?

Re: Stored Process drop down lists

Re: If nobs is 0 then create a new observation

Re: IF THEN CONDITION

Re: SAS Enterprise Guide. What happens when one submits a SAS job

Re: Macro Do Loop

Re: Converting date to number forth and back

Re: where vs. if

Re: How to count from file inside SAS?

Re: add double quotes for words in string

Re: missing,nonmissing and unique value in column

Re: In a Stored Process, using _webout and <select> statement how do I...

Re: How to change the WORK library folder location based on user

Re: [Feature suggestion] Implement VIM in the SAS base and SAS Enterpr...

Re: How to tell sas read missing values?

Re: Extract id_sequence variable from previous_id and next _id variabl...

Re: prepping census data aggregated by multiple groups

Re: How filter rows with dates

Re: Data Input in SAS

Re: log transformation

Re: How to substrate two variables by keywords in variable name?

Re: find lowest value in multple columns

Re: Grouped macros variables and make arithmitics operations

Re: Different date types in one variable

Re: Different date types in one variable

Re: Different date types in one variable

Re: Using the content of a variable for a Macro Call

Re: Formats

Re: Formats

SAS Analytics Explorers