Hi, I have a rather simple macro that I am using to run a proc sql. I am not able to resolve the error. Any help would be appreciated. %macro _countrec_updatetaxid(taxid); proc sql; create table work.extract as ( select count(*) :into rec_cnt from &FLDR..&FLDR._mcid_etg where etg1tax contains ('&taxid') ); QUIT; %mend _countrec_updatetaxid; *%local taxid rc; %let taxid=43612589; %let rc=%_countrec_updatetaxid(&taxid); %put "The number of records for tax id is" &rc; The error is NOTE: Line generated by the invoked macro "_COUNTREC_UPDATETAXID". 29         proc sql; create table work.extract as  ( select count(*) :into rec_cnt from &FLDR..&FLDR._mcid_etg where etg1tax                     ______                      180 29       ! contains  ('&taxid')  );  QUIT; ERROR 180-322: Statement is not valid or it is used out of proper order. I think the bug is small but I am not able to notice what is wrong with the code. Thanks, saspert. PS: The bigger plan is to invoke the above macro for all the records of a dataset using call execute statement. That is why I am testing this macro first. Message was edited by: saspert So here is the crux of my issue. I have a main sas dataset &FLDR..&FLDR._mcid_etg with several hundreds of taxids. Some of them (around 83 distinct taxids) have a missing leading 0 for them. Now, I have all 83 of them in a separate dataset called work.msng_taxid. So, I need to somehow do this repetitive task of fetching 1 taxid, counting the # of records in &FLDR..&FLDR._mcid_etg and updating it with the correct taxid. In the above example, 43612589 needs to be replaced with 043612589. ... View more

Hi, One of my colleagues is getting a syntax error with proc sql. The code and the error is below - substr(a.PROV_ID,(position('~' in A.PROV_ID )+1),3) as MBR_PCP_SITE                                   79                                   76   ERROR 79-322: Expecting a SELECT. ERROR 76-322: Syntax error, statement will be ignored. Any suggestions would be appreciated. The DB engine is teradata. Thanks, saspert. ... View more

Hi, I have an issue writing data to teradata. Please review my code. The idea is to take all the data from a dataset and create a volatile table in teradata. This volatile table is then used in a number of proc sql blocks later on in the code. All the proc sql blocks return 0 records and my colleagues are guessing it may be because of the format for this one column MCID. The MCID in teradata is character (for our purpose). But the FINALMBR dataset has it as numeric. So, my code (below) replaces the old code (in comments). libname &userid clear; LIBNAME &userid TERADATA user="&userid.@LDAP" password=&password TDPID=DWPROD2 CONNECTION=GLOBAL DBMSTEMP=YES; DATA FINALMBRmcids (drop=MCIDold); SET &FLDR..FINALMBR (keep=MCID rename=(MCID=MCIDold)); MCID=put(MCIDold,11.); RUN; DATA &userid..FINALMBRmcids; SET FINALMBRmcids ; RUN; /* DATA &userid..FINALMBRmcids; SET &FLDR..FINALMBR (keep=MCID); RUN; */ But the issue with the proc sql blocks returning 0 records is still there. Any suggestions would be appreciated (maybe alternative ways of loading teradata). Thanks, saspert ... View more

Hi, One of the datasets I am working with has julian dates. I am using the functions like this - put(DATEJUL(x),mmddyy10.) The query builder in EG is validating the function usage correctly but the output is like this - 01/01/7442 01/01/7445 01/01/7447 01/02/7445 01/02/7447 01/03/7442 01/03/7445 Any suggestions as to get the correct output? Thanks, Raj. ... View more