Hey folks, I am using the following macro a lot for data integrity queries. Note that var1 and var2 are used in a different manner. Now I'd like to generalize it in a way, that I can specify m variables of type 1 and n variables of type 2, i.e. the marco call should be something like name (Quelle, m, var_1, ... , var_m, n, var_m+1, var_m+n, Resultvar, Ergebnis). The important thing is that the number of variables in the macro call should depend on the numbers m and n in the macro call itself. Can that be done? thx, Stefan %Macro doppel (Quelle, Var1, Var2, Resultvar, Ergebnis); proc sql;       /* Selektion aller vorhandenen, verschiedenen Paare von Var1 und Var2 */       create table paare as       select distinct &Var1, &Var2       from &Quelle;       /* Selektion aller Var1 mit mehreren Var2 */       create table doppel as       select distinct &Var1, count(*) as Anzahl       from paare       group by &Var1       having Anzahl > 1;             /* Selektion "Var1", "Var2" und zusätzlicher "Resultvar"iable mit mehrfachen "Namen" */       create table &Ergebnis as       select distinct r.&Var1, r.&Var2, r.&resultvar       from &Quelle as r       inner join doppel       on doppel.&Var1=r.&Var1; quit; %Mend doppel; ... View more

Dear experts, I am trying to update a table with values from a different table by using a subquery.              proc sql;       /* Update durchführen */       update p_ctryexp as c             set RT_Kusa = (select RT_Kusa from tempis),                   RT_name = (select RT_Name from tempis),                   RT_IntRating = (select RT_IntRating from tempis),                   Sektor = (select Sektor from tempis)             where substr(c.KontoID, 1, max(1,length(c.KontoID)-4)) = (select KontoID                                                                                                                                       from tempis as t                                                                                                                                       where substr(c.KontoID, 1, max(1,length(c.KontoID)-4)) = t.KontoID);    quit; Now, the update fails because the subquery evaluates to more that one row: ERROR: Subquery evaluated to more than one row. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 87         quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time):       real time           0.00 seconds       cpu time            0.18 seconds I cannot follow that, because the way I understand it, the subquery is executed on each row. However for the two rows in tempis, there is exactly onw corresponding row in p_ctryexp. The inner join of the two tables on the where-condition returns two rows. What did I do wrong? Thx, Stefan ... View more

Hi experts, I am filtering on some character variables       /* Import der Matchingdaten zw. KontoID/System und fdb-Nummer */       data &workbib..i_gls_fdb2;             set kris_lls.D487_&stichmonat._GLS_VB (Keep =   syst_source iden_source_system fdb_nr ref_fdb_nr ref_fdb_rolle gls_lfs_vb_nr                                                                                     referenz_nr ogid_risk_point isin LFS_nr);             if (not fdb_nr = . or not ref_fdb_nr = 0) and ( not iden_source_system = "" or not gls_lfs_vb_nr = "");       run; However I am getting the following Notes (the IF-line is NR. 20): NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).       20:55   20:85   NOTE: Invalid numeric data, iden_source_system='DE000LB0AEB3_T' , at line 20 column 55. NOTE: Invalid numeric data, gls_lfs_vb_nr='DE000LB0AEB3' , at line 20 column 85. syst_source=ARENA_P iden_source_system=DE000LB0AEB3_T ogid_risk_point=24542733 fdb_nr=. lfs_nr=. ref_fdb_rolle=. ref_fdb_nr=. gls_lfs_vb_nr=DE000LB0AEB3 isin=  referenz_nr=0 lfs=FEHLT _ERROR_=1 _N_=2 So why does SAS convert my Character values to numeric values? Is it because the first line in the table contains (coincidentally) just numbers? How can I prevent that from happening? Thx a lot, Stefan ... View more

I am using a simple sql query with a macro variable in the FROM statement: %LET vormonat = 201303; %LET dynmonat = vormonat; proc sql;      create table &workbib..i_fdb2bereich as             select distinct bp_bereich, bp_fdb_nr             from lls_com._&&&dynmonat.._EGK_VB_GESAMT             where bp_referenz_nr = 0; /* Berater für Basket auswählen */ quit; 1st step: lls_com._&&&dynmonat.._EGK_VB_GESAMT && resolves to & &dynmonat. resolves to vormonat 2nd step: &vormonat._EGK_VB_GESAMT I get the error:  45         create table &workbib..i_fdb2bereich as 46                select distinct bp_bereich, bp_fdb_nr 47                from lls_com._&&&dynmonat.._EGK_VB_GESAMT NOTE: Line generated by the macro variable "DYNMONAT". 47          lls_com._&Vormonat                      _                      22                      76 ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, ',', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP, HAVING,               INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.  ERROR 76-322: Syntax error, statement will be ignored. Whats wrong? Thx, Stefan ... View more