About Saranya_Sub

Saranya_Sub · ‎04-04-2022

Hi , Am confused with how the Hash and Merge works for this below table . For me the output of the Merge is correct . but Merge requires the data set to be sorted before the Merge to happen and its time consuming .Is there any option i can have the same output of Merge in hash join as well? data product; input ID $ File_Nm $ PRODUCT $; datalines; 1001 File1 1234 1001 File1 4567 1001 File1 7890 1001 File1 0123 1001 File1 3456 1001 File1 6789 ; data YEAR2021_WW01; input ID $ File_Nm $ Amount $; datalines; 1001 File1 100 1001 File1 200 1001 File1 300 ; data want_hash; if 0 then set product ; declare hash p (dataset:"product"); p.definekey("ID","File_Nm"); p.definedata(all:'Y'); p.definedone(); do until(eof); set YEAR2021_WW01 end=eof; if p.find() = 0 then output; end; stop; run; /*Merge Join*/ DATA WORK.want_merge; MERGE WORK.product(IN=A) YEAR2021_WW01(IN=B); BY ID File_Nm; IF A; RUN;

Saranya_Sub · ‎06-21-2021

Hi, We are trying to encrypt a data using AES algorithm with secret key.We are storing the secret key in the keystore . We have to access the keystore to get the key for encryption using groovy in RTDM .is there any way to access the keystore in realtime using groovy ? Thanks in advance, Saranya

Saranya_Sub · ‎05-19-2020

Hi Chris, Thanks for the response . Please find below details of the fullstimer when i tried to load ~3mb XML File Total Table created : 54 in which more than 14 tables have ~18000 records and remaining tables have ~1000 records NOTE: PROCEDURE COPY used (Total process time): real time 4:03.72 user cpu time 4:00.68 system cpu time 1.17 seconds memory 32842.37k OS Memory 52888.00k Timestamp 19/05/2020 03:14:43 PM Step Count 4 Switch Count 802 Page Faults 3 Page Reclaims 17172 Page Swaps 0 Voluntary Context Switches 3662 Involuntary Context Switches 27536 Block Input Operations 504 Block Output Operations 56512 SAS Version : 9.4_M2 total memsize is MEMSIZE=3221225472 in our server Thanks, Saranya

Saranya_Sub · ‎05-15-2020

Hi All, I have a requirement to convert a ODM xml file to sas dataset. i have used below method to do that, but the execution time is more as it is a 25MB file. Is there any way to do it faster. filename NHL "/path/ODM.xml"; filename map "/path/odm_map.map"; libname NHL xmlv2 xmlmap=map; proc copy in=nhl out=work; run; ODM.xml is a 25 mb file so when i try to copy those datasets present in NHL to work library, it's taking allmost 10 min, is there any way to reduce the execution time.

Saranya_Sub · ‎05-14-2020

hi , did the problem resolve ,am facing the same issue now copy from XML engine to SAS is taking huge time

Saranya_Sub · ‎11-30-2019

%MACRO XML_Parse(_dir=); filename dir "&_dir"; data work.dir; length name $100; d=dopen("dir"); num=dnum(d); retain count 0; do i=1 to num; name=dread(d,i); r=index(name,".xml"); if r ne 0 then do; call symput('XMLNAME'||left(i), scan(name,1,'.')); output; end; end; proc sql; select count(*) INTO :_totalxml from work.dir; quit; %LET _totalxml=&_totalxml; %do _XMLLOOP = 1 %TO &_totalxml; filename xmlfile "&_dir.&_XMLNAME..xml"; filename mapfile "&_dir.&_XMLNAME..map"; libname xmlfile xmlv2 xmltype=xmlmap xmlmap=mapfile automap=replace; ---CODE TO JOIN ALL THE TABLES USING THE ORIDIANL COLUMN TO BRING TO ONE TABLES %END; %MEND; %XML_Parse(_dir='/ccc/ccc/cc'); This code will read each of the XML file ,when first XML processing is completed it moves to the next one. now my question is is there any way to run the macro in parallel so that i can read all the process all the XML file in same time Sequential(above code) XML 1 Parallel( i want it in this way) XML 2 --- > XML 1 XML 2 XML 3 XML 3 though there are some document in the web ,am not able to understand clearly. Please help Thanks, Saran

Saranya_Sub · ‎11-30-2019

Hi, i have around 10 XML file in a folder ,i have to loop through each of the XML and create SAS Dataset , sequentailly am doing it and the output is good.Now i have run the same macro for 10 XML file in parallel.10 sessions has to be created. am not sure how to do the same. can any one guide me in doing that . Thanks, Saran.

Saranya_Sub · ‎10-23-2018

Hi All, My requirement was to insert record into oracle table using DS2 code. and the below code runs in loop and insert record package _test /overwrite=yes; dcl package SQLSTMT _slct1('INSERT INTO DSDATA."SCHEMANEME"."TAB_NAME" ("A", "B", "C","D") VALUES (?,?,?,?)'); method execute (varchar(32767) A, varchar(32767) B, varchar(32767) C, varchar(32767) D); _loop=sum(countc(A,','),1); do i = 1 to _loop; A1= scan(A,i,','); B1= scan(B,i,','); C1= scan(C,i,','); D1= scan(D,i,','); _slct1.setdouble(1,A1); _slct1.setdouble(2,B1); _slct1.setvarchar(3,C1); _slct1.setvarchar(4,D1); rc=_slct1.execute(); end; endpackage;run; This was working fine and successfully inserted into oracle table.due to requirement change the Column 'C' is made as Number in oracle. So when i changed my DS2 code _slct1.setdouble(1,A1); _slct1.setdouble(2,B1); _slct1.setdouble(3,C1); _slct1.setvarchar(4,D1); rc=_slct1.execute(); its inserting NULL . i tried converting the column to Number as below declare int NUMERIC_C; NUMERIC_C=inputn(C1,10.); (or) NUMERIC_C = compress(C1); _slct1.setdouble(3,NUMERIC_C); still no luck it inserted only NULL,but the remaining double values are inserting as expected. Can someone pleases help me in this !!! Thanks

Saranya_Sub · ‎10-02-2018

Hi All, Is there any way to get the execution time of each of the SQL code that is submitted using ds2 procedure ?

Saranya_Sub · ‎08-12-2018

Hi SAS42 , Did you find the soultiong for this, am facing the same issue now.can you please help

Saranya_Sub · ‎05-13-2018

Am trying to invoke campaign via SOAP request ,which is suppose to insert data into data warehouse,but it's not happening.am using DS2 code for it When I try run the same campaign in test mode in SAS CI it's happening I want the log that gets generated via SOAP request.can anyone let me know the path for the same. Thanks in advance.

Saranya_Sub · ‎02-06-2017

i want to replace certain character(Example | to space) in the teradata table.is it possible to do that in the above append statement itself?

Saranya_Sub · ‎01-11-2017

Hi, Am trying to extract data from oracle into a dataset and append the data into Teradata . since some of the native funtionality of teradata is not working when connecting to orcale.so ended up in pulling data to SAS and then upload to terdata. proc sql; connect to oracle (PATH=XXXX USER=XXXX PASSWORD="XXXX" PRESERVE_COMMENTS ); create table work.OracleTabExtract as select * from connection to oracle( select /*+ parallel(16)*/ * from oracle.tablename ) ; i want to convert all columns present in the oracle table to character if its numeric.since the date columns are not getting loaded properly into teradata as its warning out. is there any idea which i can include in the above step? the problem is i have more than 300 columns in the table Thanks in advance

Saranya_Sub · ‎01-06-2017

Yes we are just using SAS as bridge between two database.certain functionality of Teradata (TPT API) is not working in our server

Saranya_Sub · ‎01-06-2017

HI All, i wanted to move a table from oracle to teradata.The tables are vey huge in size more than 200 GB i tried the below code proc append base=tlib.LOADTEST2 (TPT=YES FASTLOAD=YES TPT_MIN_SESSIONS=2 TPT_MAX_SESSIONS=6 TENACITY=1 SLEEP=5 TPT_ERROR_TABLE_1 = LOADTEST_ET TPT_ERROR_TABLE_2 = LOADTEST_UV TPT_LOG_TABLE = LOADTEST_RS TPT_TRACE_LEVEL=2 TPT_TRACE_LEVEL_INF=12 TPT_TRACE_OUTPUT='C:\SAS_TPT_fastload.txt') data=OLIB.Master_Oracle_Table(orhints='/*+ parallel(16)*/'); run; tlib--> Teradata library (pre assigned it) olib -->oracle library (pre assigned it) Though its working,i couldnt fetch the table which has more than 250+ columns when i run the code ,its says -No sufficient memory in SAS Can any one let me know the reason for the same? any thing i have change in the code to avoid it

Online Status	Offline
Date Last Visited	‎04-20-2022 01:37 PM

Hash and Merge

Access Keystore Realtime - Proc Groovy

Re: XML Engine Performance

XML Engine Performance

Re: Copying data from XML library to other permanent library

Re: Parallel Processing

Parallel Processing

SAS DS2 Insert Problem

Sas DS2

Re: How to convert character datetime to SAS timestamp using proc ds2?

Re: Hash and Merge

Re: Hash and Merge

Re: Parallel Processing

Re: Parallel Processing

Re: XML Engine Performance

Hash and Merge

Access Keystore Realtime - Proc Groovy

Re: XML Engine Performance

XML Engine Performance

Re: Copying data from XML library to other permanent library

Re: Parallel Processing

Parallel Processing

SAS DS2 Insert Problem

Sas DS2

Re: How to convert character datetime to SAS timestamp using proc ds2?

Soap request logs

Re: SAS implicit Pass trhough

Extraction from Oracle

Re: SAS implicit Pass trhough

SAS implicit Pass trhough