About gamotte

gamotte · ‎12-16-2019

You can generate codes for the missing combinations as follows : data temp; if _N_=0 then set have1; /* retrieve column formats */ set have2; keep varX varY; do i=1 to countw(varX1,'/'); varX=scan(varX1,i,'/'); output; end; run; proc sql noprint; CREATE TABLE new_vals AS SELECT DISTINCT * FROM temp WHERE varX NOT IN SELECT varX FROM have1; SELECT max(input(substr(varY,2),best.))+1 INTO :maxindex FROM have1; quit; data new_vals (drop=index); set new_vals; if _N_=1 then index=symgetn('maxindex'); varY=cats('C', index); index+1; run; proc append base=have1 data=new_vals; run;

gamotte · ‎12-16-2019

Here is another approach with a format generated by the dataset have1 : data ctrl; length label $ 11; set have1(rename=(varX=start varY=label)) end=last; end=start; retain fmtname 'CN' type 'c'; output; if last then do; hlo='O'; label='C0'; output; end; run; proc format library=work cntlin=ctrl; run; data want; set have2; length newvar $20; do i=1 to countw(varX1,'/'); call catx('/', newVar, put(scan(varX1,i,'/'),CN.)); end; run;

gamotte · ‎12-16-2019

You can use the coalescec function : proc sql noprint; CREATE TABLE want AS SELECT a.varX1, catx('/', coalescec(b.varY,'C0'), coalescec(c.varY,'C0'), coalescec(d.varY,'C0')) AS newVar FROM have2 a LEFT JOIN have1 b ON b.varX=scan(a.varX1,1,'/') LEFT JOIN have1 c ON c.varX=scan(a.varX1,2,'/') LEFT JOIN have1 d ON d.varX=scan(a.varX1,3,'/') ORDER BY a.varX1 ; quit;

gamotte · ‎12-16-2019

Hello, proc sql noprint; CREATE TABLE want AS SELECT a.varX1, cats(b.varY,'/',c.varY) FROM have2 a LEFT JOIN have1 b ON b.varX=scan(a.varX1,1,'/') LEFT JOIN have1 c ON c.varX=scan(a.varX1,2,'/') ORDER BY a.varX1 ; quit;

gamotte · ‎12-13-2019

Good point, I overlooked this difference in performance. The drawback i saw with proc append is the necessity to use as many proc appends as there are datasets to append. A macro can be used though.

gamotte · ‎12-13-2019

Hello, data want; set qtr1_sales qtr2_sales qtr3_sales qtr4_sales; run;

gamotte · ‎12-13-2019

Hello, The following program creates new arrays where all non missing values are consecutive so that there are only missing values at the end of arrays => The chronology only has to be checked until a missing value is met. data have; informat E1Date E2Date E3Date E4Date E5Date date9.; format E1Date E2Date E3Date E4Date E5Date date9.; input ID E1Date E1Time E2Date E2Time E3Date E3Time E4Date E4Time E5Date E5Time; cards; 1 05JAN15 1715 05JAN15 2130 05JAN15 2355 06JAN15 0100 06JAN15 0300 2 15MAY11 1300 . . . . 15MAY11 1523 15MAY11 1633 3 09FEB13 . 09FEB13 . 09FEB13 0800 09FEB13 1015 09FEB13 1145 4 18JUN17 . . . . . 18JUN17 . 18JUN17 . ; run; %macro supp_miss(array_in, array_out); i=0; call missing(of &array_out.(*)); do over &array_in.; if not missing(&array_in.) then do; i+1; &array_out.(i)=&array_in; end; end; %mend; %macro check_chronology(array, flag); &flag.=1; do i=1 to dim(&array.)-1 until(exit); if missing(&array.(i+1)) then exit=1; else do; &flag.=&flag. and (&array.(i+1) ge &array.(i)); end; end; %mend; data want; set have; format dt1-dt5 datetime.; array dates E1Date E2Date E3Date E4Date E5Date; array times E1Time E2Time E3Time E4Time E5Time; array datetimes dt1-dt5; array nmissdates(5) _TEMPORARY_; array nmissdatetimes(5) _TEMPORARY_; i=0; do over dates; if not missing(dates) and not missing(times) then do; datetimes=dhms(dates, 0, 0, times); end; end; %supp_miss(dates, nmissdates); %supp_miss(datetimes, nmissdatetimes); %check_chronology(nmissdates,flag_dates); %check_chronology(nmissdatetimes,flag_datetimes); run;

gamotte · ‎12-13-2019

Hello, data _NULL_; set have; length _NAME_ $32; call execute('data want; set have; rename'); do while(_NAME_ ne "_NAME_"); call vnext(_NAME_); if substr(_NAME_,1,3)="ID_" then do; i+1; call execute(cats(_NAME_,'=ID_',i)); end; end; call execute('; run;'); stop; run;

gamotte · ‎12-12-2019

Hi, @PaigeMiller and thanks for your kind comments. I don't know why the columns are ordered that (unintuitive) way. It seems that the possible values are first looped on the third column, then the fourth, fifth and so on... second and first columns are the last ones where the loop on values occurs. Here is an (incomplete) example with more values and columns I guess it may just be a consequence of the particular algorithm used to perform cartesian product.

gamotte · ‎12-12-2019

Hello, data have; input a_a a_b a_1 a_2 a_1000; cards; 1 2 3 4 5 ; run; data _NULL_; call execute('data want; set have; rename'); do until(fend); set sashelp.vcolumn end=fend; where libname="WORK" and memname="HAVE" and NAME like 'a_%'; call execute(cats(NAME,'=b_', substr(NAME,3))); end; call execute('; run;'); stop; run;

gamotte · ‎12-12-2019

Hello, data bin; input b; cards; 0 1 ; run; proc sql noprint; CREATE TABLE fruits AS SELECT a.b AS Apples, b.b AS Oranges, c.b AS Bananas, d.b AS Kiwis FROM bin a, bin b, bin c, bin d ; quit;

gamotte · ‎12-06-2019

Or more simply data want; set have; std_v1=sum(std_v1,0); std_v2=sum(std_v2,0); std_v3=sum(std_v3,0); run; If the columns do not exist they will be created automatically.

gamotte · ‎12-06-2019

Hello, data std_zeros; if _N_=0 then set have nobs=n; do i=1 to n; std_v1=0; std_v2=0; std_v3=0; output; end; drop i; run; data want; merge std_zeros have; run;

gamotte · ‎12-06-2019

If you want to pass a comma separated list to your macro and use it as is you can use the %quote function in the macro call to avoid interpreting the comma as an argument separator. See the second example in my previous answer.

gamotte · ‎12-06-2019

The comma is interpreted as separating two arguments "a=1" and "2" to the macro call. You can use a quote function to avoid this or chose another separator (e.g. space). %macro mac(a=); %put &=a.; %mend; %mac(a=1,2) /* two parameters => boom! */ %mac(a=%quote(1,2)) /* one parameter */ %mac(a=1 2) /* two parameter */

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: Match the corresponding values and concatenate by '/'

Re: Match the corresponding values and concatenate by '/'

Re: Match the corresponding values and concatenate by '/'

Re: Match the corresponding values and concatenate by '/'

Re: Append data sets together

Re: Append data sets together

Re: Check chronology of multiple event dates within a row?

Re: rename multiple variables with a series of numbers

Re: How to create table of every combination of 0s and 1s?

Re: Rename all variables starting with the same preffix to a different...

Re: How to create table of every combination of 0s and 1s?

Re: add new columes with value with 'if' conditons

Re: add new columes with value with 'if' conditons

Re: ERROR: All positional parameters must precede keyword parameters.

Re: ERROR: All positional parameters must precede keyword parameters.

SAS Analytics Explorers