Hello,   I'm working on a code that will help determine if a table exists in a database or not. If it does, then the table is deleted, if it doesn't exist then it creates that table:   libname db1 teradata user="xxx" password="xxx" tdpid=rchtera mode=ansi database=xx; %macro check_table(db_name,table_name); %if %sysfunc(exist(&db_name.&table_name.)) %then %do; %put The table exists.; proc sql; drop table (&db_name.&table_name.) ; quit; %end; %else %do; %Put Table does not exist; %end; %mend; %check_table(db1,T1_data); proc sql; connect to teradata (user="xx" password="xx" tdpid=RCHTERA mode=teradata); create table db1.T1_data (FASTLOAD=YES) as select * from work.T1_data; disconnect from teradata; quit; When I test this, even though the table  db1.T1_data exists, it still keeps displaying 'Table does not exist' as output. Why would the exist condition not work? I also checked to see that the correct values of &db_name.&table_name. are being passed into the macro. Appreciate the help in helping me understand what I'm missing! Thank you. ... View more

Hello mentors,   Requesting some guidance.   I have a dataset ’abc’  with dates which looks like this: DT 23Nov2019 27Nov2019 27Nov2019 27Nov2019 27Nov2019 08Nov2019 28Nov2019   Where DT is num date9.   I have a macro variable ‘peeps’ such that the value of peeps when resolved is DT = ‘2019-11-28’ or DT = ‘2019-11-23’   What I’m trying to do is if any of the dates in my dataset match up with the date values in the peeps macro variable, then flag=1. The value of peeps cannot be altered since it’s being used by other folks too.   Desired output: DT flag 23Nov2019  1 27Nov2019   27Nov2019   27Nov2019   27Nov2019   08Nov2019   28Nov2019  1   I tried the following approaches to solve for this:   Trial 1: Data abc; Set abc; If (&peeps.) then flag=1; Run;   This gave me this output: DT flag 23Nov2019   27Nov2019   27Nov2019   27Nov2019   27Nov2019   08Nov2019   28Nov2019     Trial 2: Data abc; Set abc; Format DT yymmddd10.; Run; Which changed my dataset to this: DT 2019-11-23 2019-11-27 2019-11-27 2019-11-27 2019-11-27 2019-11-08 2019-11-28   Then when I tried this, I get the same output as above: Data abc; Set abc; If (&peeps.) then flag=1; Run; Result: DT Flag 23Nov2019   27Nov2019   27Nov2019   27Nov2019   27Nov2019   08Nov2019   28Nov2019     Trial 3: data abc; set abc; DT2=cat("'" , put(DT,yymmddd10.),"'" ); run; data abc; set abc(rename=(DT=MAIN_DT DT2= DT)); run; which converted abc dataset to: MAIN_DT DT 23Nov2019 '2019-11-23' 27Nov2019 '2019-11-27' 27Nov2019 '2019-11-27' 27Nov2019 '2019-11-27' 27Nov2019 '2019-11-27' 08Nov2019 '2019-11-08' 28Nov2019 '2019-11-28'   Followed by: Data abc; Set abc; If (&peeps.) then flag=1; Run; Same empty flag Result: DT DT   flag 23Nov2019 '2019-11-23'     27Nov2019 '2019-11-27'     27Nov2019 '2019-11-27'     27Nov2019 '2019-11-27'     27Nov2019 '2019-11-27'     08Nov2019 '2019-11-08'     28Nov2019 '2019-11-28'       I also tried replacing 'peeps' by the actual macro variable value with all the above 3 trial methods, but still not able to get the desired result.   Data abc; Set abc; If DT = ‘2019-11-28’ or DT = ‘2019-11-23’ then flag=1; Run;   Appreciate the help, thank you! ... View more

Hello mentors,   I have a question regarding check for empty formats and would appreciate your guidance    I have datasets that contain ‘id’ values only and I have created separate formats from those id datasets. There are 6 datasets: table 1-6, they all contain a column called id (char) such that: -same id value could be present in multiple tables -the tables are being created by some other process, so they could be blank.   %macro crt_frmt(ds); proc sort data=&ds. nodupkey; by id; run; data &ds._format; set &ds.; fmtname = "$&ds._format"; type = 'c'; start = strip(id); end = strip(id); label = 'YES'; run; proc format cntlin= &ds._format; run; %mend; %crt_frmt(table1); %crt_frmt(table2); %crt_frmt(table3); %crt_frmt(table4); %crt_frmt(table5); %crt_frmt(table6); I then use these table formats to flag observations and in the later part of the code delete and do other things my master dataset using these formats:   data master; set master; if put(id,$table1_format.) = "YES" then flag=1; if put(id,$table2_format.) = "YES" then flag=2; if put(id,$table3_format.) = "YES" then flag=3; if put(id,$table4_format.) = "YES" then flag=4; if put(id,$table5_format.) = "YES" then flag=5; if put(id,$table6_format.) = "YES" then flag=6; run; proc freq data=master; table flag /list missing; run; The issue is, there are times when some tables are empty, so that creates an empty format. When I create flags in the ‘master’ datastep, it gives me an error: "ERROR: The format $table1_format was not found or could not be loaded"   Which is understandable since it was an empty dataset. But how do I conditionally run the put statements in my master dataset so that they run only when formats are present?   I tried adding this within the macro:   %let dsid=%sysfunc(open(&ds.)); %let &ds._format_nobs=%sysfunc(attrn(&dsid,nlobs)); %let dsid=%sysfunc(close(&dsid)); %put &ds._format_nobs= &ds._format_nobs; Such that in the master dataset I could do:   data master; set master; if table1_format_nobs>0 then do; if put(id,$table1_format.) = "YES" then flag=1; end; : : But this obviously isn’t right, since table1_format_nobs is a macro variable and cannot be referenced this way.   Appreciate the help and time!   Thank you. ... View more