Hi,   This response is pretty similar to dcruik's response (I think we both started responding around the same time).    In any case, I added some sample data. Also, placing the comma before the derivation_1, derivation_2, etc. statement is handy in that it avoids the need to add an "%else %do" statement. Enjoy.       %let num = 4; *fictitious value; %let standard_vars = %STR(abc, xyz); *fictitious variables; /* fictitious dataset to mimic yours */ data edd_derivations; do i=1 to 200; event_type = round(ranuni(123)*25); output; end; run; proc sql; select count(*) into :var_count from edd_derivations where event_type=&num.; quit; %put &var_count; /* fictitious dataset to mimic yours */ data _events_; length event_type abc xyz $8; do j=1 to 500; event_type = cats(round(ranuni(456)*25), ""); abc = byte(65 + mod(j, 26)); xyz = byte(90 - mod(j, 26)); output; end; run; /* this version, "DynamicSQL1()", shows the general approach */ %MACRO DynamicSQL1(); proc sql; create table _&num. as select &standard_vars. %IF %EVAL(&var_count > 0) %THEN %DO; %DO k=1 %TO &var_count; , CASE WHEN /* some condition goes here [e.g. " abc in ('A','B','C')" ] */ THEN ':-)' ELSE ':( ' END AS name_&k. %END; %END; from _events_ where event_type="&num." ; quit; %MEND; %DynamicSQL1(); /* this version, "DynamicSQL2()", is intended to mimic your example; I wasn't sure if your derivation_1, derivation_2, etc. variables were dynamic or stayed constant throughout */ %MACRO DynamicSQL2(); /* these macro variables are silly placeholders; please replace with your own logic */ %let derivation_1 = %str(CASE WHEN 1=2 THEN 'sad' ELSE 'happy' END); %let derivation_2 = %str(CASE WHEN 2=3 THEN 'sad' ELSE 'happy' END); %let derivation_3 = %str(CASE WHEN 3=4 THEN 'sad' ELSE 'happy' END); %let derivation_4 = %str(CASE WHEN 4=5 THEN 'sad' ELSE 'happy' END); %let derivation_5 = %str(CASE WHEN 5=6 THEN 'sad' ELSE 'happy' END); proc sql; create table _&num. as select &standard_vars. %IF %EVAL(&var_count > 0) %THEN %DO; %DO k=1 %TO &var_count; /* note the double ampersand before the derivation variable ... */ , &&derivation_&k. AS name_&k. %END; %END; from _events_ where event_type="&num." ; quit; %MEND; %DynamicSQL2();     ... View more

Since you are using an "explicit" rather than "implicit" pass-through SQL statement, your double quotes are being interpreted as a quoted identifiers. In ANSI-compliant SQL databases, double quotes are reserved for object/column names that contain non-standard characters. An example of an ANSI-compliant column name would be CUSTOMER_ID_NUM (quotes are optional); an example of a non-compliant column name would be "customer-id-#" (in which case, quotes are mandatory).    in your PROC SQL statement ...     change:  &where    to:      %SYSFUNC(TRANSLATE(&where, %BQUOTE("'), %BQUOTE('")))     Alternatively, you should avoid using double quotes altogether, for anything that follows the "connection to ODBC ... " statement.         %macro data (start_date, where, outdata); PROC SQL; CONNECT TO ODBC (USER=xxxxxxxxx PW=xxxxxxxxx DSN=xxxxxxx); CREATE TABLE DATA1 AS SELECT * FROM CONNECTION TO ODBC ( SELECT DISTINCT (ESP01.CNTRCT_CONTRACT_R||ESP01.VEH_VIN_C) AS CONTRACT_VIN, EXTRACT(YEAR FROM ESP01.CNTRCT_START_Y) AS START_YEAR, CAST ( (ESP01.&START_DATE (FORMAT 'YYYYMM') ) as CHAR(6) ) as START_DATE, /*Contract start time for new vehicle*/ ESP01.PLAN_CNT_CVG_TME_R as term FROM ESPARS_View.SSEPR01 as ESP01 WHERE ESP01.CNTRCT_TEST_MKT_F = 'N' /* necessary constraint to get rid of the free contracts*/ AND %SYSFUNC(TRANSLATE(&where, %BQUOTE("'), %BQUOTE('"))) /*Macro variable for all conditions to filter the contracts*/ AND ESP01.PLBRN_PRDCT_LINE_C IN ('C', 'E', 'F')/* necessary constraint to pull the right contracts*/ order by 1,2 ); DISCONNECT FROM ODBC; QUIT; %mend;     ... View more

Did you find the answer here:    https://communities.sas.com/t5/SAS-Procedures/Compare-two-columns-first-alphabetically/m-p/237473 ?   ... View more

This implements contributor ndp's suggestion. It also handles reordering of columns to the way you want them to appear.    Beware that the variables created by PROC TRANSPOSE, regrettably have a hyphen in them: Fico_2014-01, rsn_cd1_2014-01, rsn_cd2_2014-01, etc.   Enjoy!      data gamdist; input cust_xref_id 1-6 Fico 9-12 rsn_cd1 13-15 rsn_cd2 17-19 Yr_Month $21-28; cards; 15013 668 39 18 2014-01 15013 668 39 18 2014-02 15013 668 39 18 2014-03 15013 670 39 18 2014-04 15013 678 39 18 2014-05 15013 638 38 20 2014-07 15013 635 38 20 2014-08 15013 635 38 20 2014-09 15013 643 38 20 2014-10 15013 643 38 20 2014-11 15013 648 38 24 2014-12 15013 648 38 24 2015-01 15013 648 38 24 2015-02 15013 651 38 24 2015-04 15013 653 38 24 2015-05 15013 653 38 24 2015-07 15013 654 38 24 2015-08 15013 661 38 24 2015-09 15013 662 38 24 2015-10 15013 662 38 24 2015-11 ; run; %MACRO DoTranspose(variable_name, dataset_name); proc transpose data=work.gamdist out=work.&dataset_name(drop=_NAME_) prefix=&variable_name._; by cust_xref_id; id Yr_Month; var &variable_name; run; %MEND; %DoTranspose(Fico, step1); %DoTranspose(rsn_cd1, step2); %DoTranspose(rsn_cd2, step3); PROC SQL; /* come up with a way to reorder variables */ /* the variable names have a hyphen (-) so variable names must look something like this: "Fico_2014-01"n */ SELECT CAT('"Fico_', STRIP(Yr_Month), '"n ', '"rsn_cd1_', STRIP(Yr_Month), '"n ', '"rsn_cd2_', STRIP(Yr_Month), '"n ') INTO :ordered_variable_list SEPARATED BY ' ' FROM gamdist ORDER BY Yr_Month; QUIT; %put &ordered_variable_list; DATA work.combined; /* reorder variables */ ATTRIB cust_xref_id &ordered_variable_list label=''; MERGE work.step1 work.step2 work.step3; BY cust_xref_id; RUN;   ... View more

You can do it in a where statement (or where option) if you use the monotonic() function. The function behaves in a roughly comparable manner to that of _n_. It even works in PROC SQL, which disallows the variable _n_!    Enjoy.      DATA have; LENGTH name $10; name='Jack'; id=555; output; name='Jill'; id=333; output; name='Bonnie'; id=777; output; name='Clyde'; id=123; output; RUN; /* version 1: PROC SQL */ PROC SQL; CREATE TABLE want1 AS SELECT * FROM have WHERE MOD(MONOTONIC(), 2) = 0; /* beware: avoid including an ORDER BY statement here */ /* because the results will be misleading */ QUIT; /* version 2: data step WHERE */ DATA want2; SET have; WHERE MOD(MONOTONIC(), 2) = 0; RUN; /* version 3: data step WHERE option */ DATA want3; SET have(WHERE=(MOD(MONOTONIC(), 2) = 0)); RUN; ... View more

Below is a relatively straightforward solution:    DATA have; LENGTH member_id $8; INPUT member_id $; DATALINES; A123 A124 B135 C134 D134 E333 ®888 ¥999 ; RUN; DATA want; SET have; letter = SUBSTR(member_id, 1, 1); SELECT; WHEN (letter IN ('A','B','C')) index = 1; WHEN (letter IN ('D','E','F')) index = 2; WHEN (letter IN ('G','H','I')) index = 3; WHEN (letter IN ('J','K')) index = 4; WHEN (letter IN ('L','M','N')) index = 5; WHEN (letter IN ('O','P','Q')) index = 6; WHEN (letter IN ('R','S','T')) index = 7; WHEN (letter IN ('U','V')) index = 8; WHEN (letter IN ('W','X')) index = 9; WHEN (letter IN ('Y','Z')) index = 10; OTHERWISE DO; PUT "WARNING: Bad character in letter - " letter; END; END; RUN; ... View more

Hi,   Give this a try. This can be done in fewer data steps, but I kept it simple for illustrative purposes. Enjoy!   hbi     DATA have; INPUT sub ev cnt dy1 dy2 dy3 dy4 dy5 val1 val2 val3 val4 val5; cards; 100 255 5 25 55 78 90 110 .5 .9 1.5 6 8 101 90 3 40 83 87 . . .6 .8 1.7 . . 103 24 2 25 30 . . . .9 .9 . . . 104 100 5 80 90 99 180 200 1.8 1.9 2 6 9 ; RUN; /* fix up dataset; find minimums; filter out unwanted computed values */ DATA temp; SET have; ARRAY dy_all dy1-dy5; ARRAY dy_diff diff1-diff5; ARRAY val_all val1-val5; i=0; DO OVER dy_all; i+1; IF (ev - dy_all > 80) THEN dy_diff[i] = .; ELSE IF (ev < dy_all) THEN dy_diff[i] = .; ELSE dy_diff[i] = ev - dy_all; END; min_diff = MIN(of dy_diff[*]); DO j=1 TO 5; IF (NMISS(min_diff, dy_diff[j]) = 0) THEN DO; IF min_diff = dy_diff[j] THEN target_var = j; END; END; IF NOT MISSING(target_var) THEN reqvar = val_all[target_var]; RUN; DATA want; SET temp; DROP dy1 dy2 dy3 dy4 dy5 val1 val2 val3 val4 val5 diff1 diff2 diff3 diff4 diff5 min_diff target_var i j; RUN; ... View more

Hi,    Since your parameters are generated dynamically, it makes sense to trap for certain invalid conditions before passing them to proc univariate. First, I added "options symbolgen mprint;" since that is helpful when looking at log output for dynamically generated variables.   Second, I added debugging code within the do loop that does a few things:   traps for two invalid conditions (&bww=0 and &lww > &upp), which proc univariate finds acceptable outputs the invalid condition to the results pane  Hope this helps. Have a look at the attached PDF file too.      %macro histo; proc sort data=test out=test1; by group; run; proc means data=test1 min max range; by group; var y; output out=dishist2(drop=_TYPE_ _FREQ_) min=min max=max range=range; run; data dishist2; set dishist2; bw=(max-min)/10; lw=floor(min/bw)*bw-(3*bw); up=floor(((max/bw)+1)*bw)+(3*bw); run; proc sql noprint ; select bw ,lw, up into :bw1 separated by ' ', :lw1 separated by ' ', :up1 separated by ' ' from dishist2; quit; %let cnt=&sqlobs; options symbolgen mprint; %do i=1 %to &cnt; %let lww= %scan(&lw1,&i); %let upp= %scan(&up1,&i); %let bww= %scan(&bw1,&i); %let bad_flag=0; %let bad_reason=""; /* flag certain invalid conditions */ %IF %EVAL(&bww=0) %THEN %DO; %let bad_flag=1; %let bad_reason=%STR(Cannot execute due to bad parameters: bww = 0); %END; %ELSE %IF %EVAL(&lww > &upp) %THEN %DO; %let bad_flag=2; %let bad_reason=%STR(Cannot execute due to bad parameters: lww > upp); %END; /* if there are other invalid condition that you want to detect, add them here: */ /* %ELSE %IF %EVAL( test_for_bad_condition_here ) %THEN %DO; */ /* %let bad_flag=3; */ /* %let bad_reason=%STR( description_of_bad_condition_here ); */ /* %END; */ %IF %EVAL(&bad_flag=0) %THEN %DO; proc univariate data=test1 noprint ; by group; histogram y/ grid normal name='MyHist' vscale=proportion hoffset=10 vaxis=0 to 1 by .5 endpoints = &lww to &upp by &bww; run; %END; %ELSE %DO; /* output explanation why procedure cannot be executed ... */ DATA work.debug_table; cnt = &cnt; lww = &lww; upp = &upp; bww = &bww; RUN; PROC PRINT DATA=debug_table; TITLE color=red "&bad_reason"; RUN; PROC SQL; DROP TABLE work.debug_table; QUIT; TITLE; *reset title; %END; %end; options nosymbolgen nomprint; %mend histo; %histo;   ... View more

I think you were missing a ", " between a.patID and a.encDate1 ...     PROC SQL; CREATE TABLE ENC_Join AS SELECT a.patID, a.encdate1 FROM Caper.OBS_12ND as A left join caper.encounterdate as b on(a.patID = b.patID and a.encdate1= b.encdate1); QUIT;   If you still get the error "Sort initialization failure", you might want to: (1) adjust memsize, or (2) add a "DISTINCT" in order to limit duplicate row output.     PROC SQL; CREATE TABLE ENC_Join AS SELECT DISTINCT a.patID, a.encdate1 FROM Caper.OBS_12ND as A left join caper.encounterdate as b on(a.patID = b.patID and a.encdate1= b.encdate1); QUIT; ... View more

Unfortunately, your custom VBA function is a something that MS Access supports while your database is open in MS Access. The ODBC driver can handle built-in functions (e.g. Now(), Trim(), etc.), but not custom functions.   I would suggest running a "make-table" statement in MS Access that calls the VBA function:    SELECT Name, Address, NameOfVBAFunction(name_of_column) AS Expr1 INTO [copy_of_table] FROM [name_of_table]; This would create a table called "copy_of_table" that contains the results of the function call. ... View more