data live_account; merge live_account (in=a) purge (in=b) plan (in=c); if a and ~b; by account_no; ; if card_type in ("PL" "TU") then do; if plan)num=60000 then outuput; end; else output; run; I would like to translate this to sql but not sure how to do it, could you please help me, thanks. ... View more

Data application (keep=org_code application_ref_no application_no credit_card_no applicant _id); Set onenov.application (encoding=any); Where credit_card_no in (’0004835199841570016’, ’0004835191842460016’, ’0000235881284822010’, ...) /* plus another 17 similar codes*/ Run; I have the above code but strangely in the output I get credit no wrong for those not end in 0 Eg 0004835199841570016 becomes 0004835199841570010 While those end in zero is correct Is the format issue? ... View more

Data nov.sample; Set nov.test; Where x=0; Run: I manage to get an observation as output from nov.test but it cannot be saved to nov.sample, says not enough authority, why? ... View more

merge ACCT_NEW_ALL_MAC(IN=A) rate_MAC DEC_ACCT_MAC_OCA_SUM2; by account_no if a Select * from ACCT_NEW_ALL_MAC A left join rate_MAC B on A.account_no=B.account_no left join DEC_ACCT_MAC_OCA_SUM2 C A.account_no=C.account_no Hi I am trying to write SAS merging three tables into SQL, please could you review for me and let me know if it make sense? Thanks. ... View more

If i have series of program that produce series of tables containing production data, instead of masking some sensitive fields, if i just make these data null, e.g. have all names become null in  the same column, is all programs still run as before given this name field is not key to any join table or a factors in any important calculation  ... View more

data _null_; if exist ('WDATA.TUEF_EA_H') then stop; else call execute ('proc sql; create table WDATA.TUEF_EA_H as select * from SDATA.TUEF_EA where 1=2;quit;'); run; in the above code, does it mean if it cannot find WDATA.TUEF_EA_H in folder WDATA, it will create one? What does 1=2 means? ... View more

if I run 10 sas program one after another triggered by one sas master program, is the temp data set I created in sas program no. 1 and not saved in folder still available to be used by sas no.10?   e.g. in sas program no. 1 proc sql;     create table _curr_credit_card as        select *        from wdata.credit_card; quit;   is _curr_credit_card still available to be used in sas program no. 10.   which says: proc append base=wdata.credit_card_h data=_curr_credit_card; run;     ... View more

data monthly.summary_ead; merge summary_ead(in=a) final_ead(in=b rename=(dawccf=final_dawccf)); by portfolio product ead; if a and not b then final_dawccf=dawccf; if not a and b then do; cnt=0; end; run; translated into SQL; Select *, b.dawccf as b.final_dawccf case when (a.portfolio is not null and a.product is not null and a.ead is not null) and (b.portfolio is null or b.product is null or b.ead is null) then final_dawccf=dawccf, case when (a.portfolio is null or a.product is null or a.ead is null) and (b.portfolio is not null and b.product is not null or b.ead is not null) then cnt=0, from summary_ead a full join final_ead b on a.portfolio=b.portfolio and a.product=b.product and a.ead=b.ead Hi all, I try to translate sas into sql but not if it is correct. Please could you give me advice. Thanks.     ... View more

PROC SQL; create table summary_ead_&p_yymmdd as select portfolio, product, ead, sum(ccf*rel_cnt)/sum(rel_cnt) as dcwccf avg(ccf) as dawccf count(*) as cnt from acct_list_final_&P_yymmdd where ead in ('PHI','ALP') group by portfolio, product, ead, quit; cnt= count(*) - no of rows in dataset but there is a clause 'where ead in ('PHI', 'ALP')' so does count(*) include only those fulfilling where ead in ('PHI', 'ALP') or all rows in dataset acct_list_final_&P_yymmdd regardless of where statement?   ... View more