About SASAna

SASAna · ‎12-16-2016

Hi SAS users, A filed "ENTRY_DT" is built as date format in Oracle Database, i am trying to load current date in YYYY-MM-DD format . I tried to convert it into character with put function & also input function. still getting te below error, where it is inserting the records into table. ENTRY_DT = put(today(),YYMMDD10.); ERROR: ENTRY_DT, a numeric column, can only be updated with a numeric expression. Thanks, Ana

SASAna · ‎12-15-2016

Thanks Asto, Macro is pretty simple with DB connectivity, insert, update & delete SQL's only. But data is huge. running 7 loops is taking extra hours.

SASAna · ‎12-15-2016

Hi Kurt, UNIX System is very good and database is also fine where i connect and do 50% of the work in my code. since the data size is 75K to 1 million records per month, i thought parallel process may help in processing macro test1 to macr test10

SASAna · ‎12-15-2016

Hi SAS users, due to heavy run time, i was thinking of trying approch of parallell processing. But my code structure is like below and not sure whether there is a chance to do parallell process or not? do loop i do loop j do loop k macro test1 ( macro test -10 are same but with 1 parameter being diff which made them run 10 times) macro test2 macro test3 .. .. macro test10 do loop ends k do loop ends j do loop ends k Thanks, Ana

SASAna · ‎12-14-2016

Thanks much. Solution seemed very easy than my complex method. Thanks, Ana

SASAna · ‎12-14-2016

&BEG - '2016-12-13' &BEG1 - '2016-11-14' I want the days to be -31 of the c_date. Thanks, Ana

SASAna · ‎12-14-2016

Hi SAS Users, I want to get one month back date and tried the below test. as c_date is not in sas date format, i am getting dot (.) in the &beg &beg1. c_Date is charcter format date ('2016-12-14') data test; c_date = '2016-12-14'; call symput('dt',strip(input(put(c_date,yymmddn8.),8.))); call symput('dt1',strip(put(c_date,WORDDATE20.))); call symputx('beg',"'"||put(intnx('day',c_date,-1,'beg'),yymmdd10.)||"'",'g'); call symputx('beg1',"'"||put(intnx('day',c_date,-30,'end'),yymmdd10.)||"'",'g'); run; %put &dt &dt1 &beg &beg1; Thanks, Ana

SASAna · ‎12-06-2016

Thanks much for the quick reply. It worked very well.

SASAna · ‎12-06-2016

Hi SAS Users, I wanted some help with getting the count of pipes '|' of a macro variable. %LET DLM_COUNT = COUNTC(&PLATFORM,') +1; &PLATFORM has value like 'ABC|DEF|GHI|QWE' So i am expecting to 4 in DLM_COUNT. Thanks, Ana

SASAna · ‎12-01-2016

I wanted to get the current date & time to insert in one of the attributes of ORacle Table. I tried below 2 options & i am getting default date of SAS. Option 1 : data_null_; RECORD_DATE = compress(put(today(),yymmddn8.)||'-'||Trim(Left(put(time(),time11.2))),':.') run; Option2 : PROC SQL; INSERT INTO DBMS.&MyTable (RECORD_DATE, Other attributes ) SELECT DATETIME(), Other attributes FROM MANUAL_TEMP B Both are not working to resolve current date & time. Thanks, Ana

SASAna · ‎12-01-2016

Hi SAS users, Need help with solving this macro in PROC SQL. PROC SQL; CONNECT TO ORACLE(PATH=XXXX USER=YYYY. ORAPW=ZZZ); execute (delete from A.&MyTable where substr(RUN_DT,1,7) = &ANA_RUN_DT (RUN_DT is a charcter field in table) ) by oracle; disconnect from oracle; QUIT; ANA_RUN_DT is 2016-11 Error - ERROR: ORACLE execute error: ORA-01722: invalid number. I tried putting "&ANA_RUN_DT" and '&ANA_RUN_DT' , but it did not work. Thanks, Ana

SASAna · ‎11-21-2016

Thanks Tom. It worked very well , you have helped me minimize many un-necessary codes too . Thanks again

SASAna · ‎11-21-2016

Hi, thats ":D" came as smiley , but i was trying to move collect all the datasets into a variable called DSET seperated by : @Reeza : this datasetep i am using it for looping & splitting as i have to pass one by one datasets & moving into a macro variable manual_files for furthur process. Manual_file is not getting resolved. Not sure if it is &DSET ? DATA UNIX_FILES; Length MANUAL_FILES $32767; %LET i=1; %do %while (&i <= (%length(compress(&DSET,~,k))) +1); (" I created DSET in previous step which has around 30 datasets sepeated by tilda "~") %let MANUAL_FILES = %scan(&DSET,&i,~); %let i = %eval(&i+1); %end; RUN;

SASAna · ‎11-21-2016

Hi SAS USers, MANUAL_FILES is not being assigned with DSET values , It is coming as blank. Below macro i am collecting all datasets & combining them by tilda delimiter in DSET & splitting them in UNIX_FILES Dataset & trying to read one by one in MANUAL_FILES macro variable. %MACRO INIT_PULL; libname mydata '/unixpath/'; proc sql; select memName into:DSET separated by '~' from dictionary.tables where libname='MYDATA' and memtype='DATA'; Quit; %PUT &DSET; DATA UNIX_FILES; Length MANUAL_FILES $32767; %LET i=1; %do %while (&i <= (%length(compress(&DSET,~,k))) +1); %let MANUAL_FILES = %scan(&DSET,&i,~); %let i = %eval(&i+1); %end; RUN; %MEND INIT_PULL; %INIT_PULL;

SASAna · ‎10-29-2016

Hi SAS users, I need help with writing this SQL correctly. I need to update/alter a table with status of 'SUCCESS/FAILURE' based on the case condition . UPDATE &SUM_TABLE A SET STAT = 'SUCCESS' case WHEN ((B.INPUT_CNT - A.UNIQUE_CLAIM_ID)=0 (SELECT COUNT(*) AS INPUT_CNT FROM MANUAL_TEMP B) ) ELSE 'FAILURE' END; Thanks, Ana

Online Status	Offline
Date Last Visited	‎06-30-2021 01:11 AM

Re: SCD type2 in SAS

SCD type2 in SAS

Re: Proc transpose

Re: Proc transpose

Proc transpose

Re: proc gee - Nesting continuous variable

proc gee - Nesting continuous variable

Re: min Date conversions to character date

min Date conversions to character date

Re: adding weeks to the max of the date and comparing it with datetime...

Re: SCD type2 in SAS

Re: Proc transpose

Re: adding weeks to the max of the date and comparing it with datetime...

Re: adding weeks to the max of the date and comparing it with datetime...

Re: scan in the proc sql

Re: scan in the proc sql

Excel reading and converting to macro variable error

Re: Converting character date to " DATETIME20."

today() to insert into date format of Oracle table

Re: parallel processing for loops & under macro

Re: parallel processing for loops & under macro

parallel processing for loops & under macro

Re: SAS a character date to go one month back

Re: SAS a character date to go one month back

SAS a character date to go one month back

Re: Count the number of pipes within Macro

Count the number of pipes within Macro

Insert Current Date & Time into Oracle record

PROC SQL - Oracle

Re: PROC SQL macro variable

Re: PROC SQL macro variable

PROC SQL macro variable

proc SQL update table with CASE when