About dhana

dhana · ‎01-10-2012

Art this is really excellent...! I didn't think in this way...! Thanks a lot ...! Regards Dhana

dhana · ‎01-10-2012

Thanks Patrick...! This actually solved my probelm. Reagrds Dhana

dhana · ‎01-10-2012

Thanks a lot Tom...! Actually I noticed and corrected couple of errors that you have mentioned here while I was running the program. I have learned a new way of creating account numbers with quotes, thanks a lot for that. I will test this and get back to you. Thanks Dhana

dhana · ‎01-10-2012

Hi Art, Thanks for your suggestion. I have attached a file with some sample records. Can you please help me to read this.

dhana · ‎01-10-2012

I have a millions of records like this (comma separated records in text file) 000000000123456789,30,ABC,2010-09-21,JANUDG_BUS2,BH1 MK1,4,201111,100,,78562561,Correct Char,987654321,1112131415,FEE,VEEFEE_BUS2,456109231,1011,LMS,1,0, 000000000987654321,20,ZXF,2011-09-21,GNYUDG_BUS2,BY1 MK1,4,201111,100,,78562561,In SAS Version 7 and beyond, SAS provides new date formats that allow you to specify the type of separator, or no separator when writing out date values,987654321,1112131415,FEE,VEEFEE_BUS2,456109231,1011,LMS,1,0, I am able to read the first record with out any problem I am able to read it with the help of DSD MISSOVER TRUNCOVER options. But if you look at the second record the highlighted character value coming with multiple commas with in the character variable, Can anyone help me to read these kind of record. Thanks Dhanasekaran R

dhana · ‎01-06-2012

You mean to say that in the program where I am calling this macro %FORMAT_DATASET, I should declare %GLOBAL ACCT_NBR_D; %GLOBAL ACCT_NBR_M; But the Error message looks to be a problem with the %LET statement. Thanks Dhanasekaran R

dhana · ‎01-06-2012

Hi All, I am in the process of setting up auto call macro library for my project. As part of that I have created macro module which will read the dataset and format the account number present in the dataset and store it in two different variable ACCT_NBR_M ($18.) and ACCT_NBR_D ($22.). Actually these two formats are to used to query different tables. Finally I am putting all the account number into a single macro variable, which I can use it in my where condition like this where account_num in (&acct_nbr_m); Below is the code I am using to do the above step. %MACRO FORMAT_DATASET(LIB=,TABLE=); PROC DATASETS LIBRARY=&LIB; DELETE &TABLE; RUN; DATA &LIB..LISTFILE(DROP=ACCT_NBR); SET &LIB..&TABLE(KEEP=ACCT_NBR); FORMAT ACCT_NBR_MQ $QUOTE20. ACCT_NBR_DQ $QUOTE24.; ACCT_NBR_MQ=PUT(INPUT(ACCT_NBR,22.),Z18.); ACCT_NBR_DQ=PUT(INPUT(ACCT_NBR,22.),Z22.); RUN; PROC SQL; SELECT ACCT_NBR_DQ INTO :ACCT_NBR_DQ SEPARATED BY "," FROM &LIB..LISTFILE; SELECT ACCT_NBR_MQ INTO :ACCT_NBR_MQ SEPARATED BY "," FROM &LIB..LISTFILE; QUIT; %LET ACCT_NBR_D=%SYSFUNC(TRANWRD(%BQUOTE(&ACCT_NBR_DQ),%BQUOTE("),%BQUOTE('))); %LET ACCT_NBR_M=%SYSFUNC(TRANWRD(%BQUOTE(&ACCT_NBR_MQ),%BQUOTE("),%BQUOTE('))); %MEND FORMAT_DATASET; The code works perfectly with out putting them inside macro, but when I placed them inside the %macro %mend and try to access them from some other location, I am getting the following error. ERROR: Macro keyword LET appears as text. A semicolon or other delimiter may be missing. Am I missing anything when I place the code inside the %macro and %mend. Please help. In the same program I have another question, the macro variable ACCT_NBR_M is not holding more than 1500+ accounts. Do we have any option in SAS to expand the macro variable storage length. Thanks Dhanasekaran R

dhana · ‎01-06-2012

Thank You SASJedi...! I will try this option and will come back if I run into any issues...! Thanks Dhanasekaran R

dhana · ‎01-03-2012

Thanks SASJedi...! This is really an excellent option and answer. I tried this option it worked perfectly. But I have faced 2 problems. 1. When I try to merge one teradata table with SAS dataset , I have found an interesting thing. Though the SAS dataset contains approximately 1 Million record and all the account numbers are present in the teradata table, the query returned only ~15718 records. Can you help me why I am getting very less record returned from the query. 2. When I try to merge 7 teradata tables along with the SAS dataset then I got the following error. ERROR: Teradata connection: TWM Workload violation: Limit of 7 concurrent sessions, for User XXXXXX, During rule state Batch-Normal. Do you have any idea why I am getting this error or any suggestion. This is the code I am using --- LIBNAME _ALL_ CLEAR; LIBNAME X "/dhana/inputfile/"; LIBNAME TD TERADATA TDPID=TERADT USER=%SYSGET(USER) PASSWORD=%SYSGET(PASSWD) SCHEMA=XXXXXXX MULTI_DATASRC_OPT=IN_CLAUSE; PROC SQL; SELECT A.VAR1,A.VAR2,A.VAR3,B.VAR1,B.VAR2,B.VAR3,B.VAR4,B.VAR5,B.VAR6,B.VAR7,C.VAR1,C.VAR2,C.VAR3,C.VAR4,D.VAR1,D.VAR2,E.VAR1,F.VAR1,G.VAR1 FROM TD.TABLE1 B LEFT JOIN TD.TABLE2 A ON A.ACCT_ID=B.ACCT_ID AND A.EFF_DT=B.EFF_DT LEFT JOIN TD.TABLE3 C ON A.ACCT_ID=C.ACCT_ID AND A.EFF_DT=C.EFF_DT LEFT JOIN TD.TABLE4 D ON A.ACCT_ID=D.ACCT_ID AND A.EFF_DT=D.EFF_DT LEFT JOIN TD.TABLE5 E ON A.ACCT_ID=E.ACCT_ID AND A.EFF_DT=E.EFF_DT LEFT JOIN TD.TABLE6 F ON A.ACCT_ID=F.ACCT_ID AND A.EFF_DT=F.EFF_DT LEFT JOIN TD.TABLE7 G ON A.ACCT_ID=G.ACCT_ID AND A.EFF_DT=G.EFF_DT WHERE B.EFF_DT='2011-11-30' AND C.VAR6='D' AND B.ACCT_NBR IN(SELECT ACCT_NBR FROM X.CCLDRIVER) ORDER BY B.ACCT_NBR, B.ACCT_ID ; QUIT; LIBNAME _ALL_ CLEAR; Thanks Dhanasekaran R

dhana · ‎01-02-2012

Happy New Year SAS Communities...! Here is my problem question I have ~90 Million record in my master table (Teradata table) and I have another SAS dataset with 1 Million record. Both of these table contains account numbers. I want to query the teradata table based on account number using the account numbers from SAS dataset. I am using the following code to do that LIBNAME X "/dhana/inputfile"; PROC SQL; RESET INOBS=MAX OUTOBS=MAX LOOPS=MAX NOFLOW NOFEEDBACK NOPROMPT NONUMBER ; CONNECT TO TERADATA(TDPID=TERADT USER=%SYSGET(USER) PASSWORD=%SYSGET(PASSWD)); CREATE TABLE X.ALL_ACCTS AS SELECT * FROM CONNECTION TO TERADATA( SELECT VAR1, VAR2, VAR3, VAR4, VAR5, VAR5 FROM TABLE1 WHERE ACCT_NBR IN( SELECT ACCT_NBR FROM X.SAS_DATASET) ) ; QUIT; It looks like I can't use the SAS dataset in the Teradata SQL pass through query. I am getting the following error message - Teradata prepare: Database 'X' does not exist. Is there any way I can subset the teradata table with the account numbers from my SAS dataset. Thanks Dhanas

dhana · ‎09-29-2011

Hi Belle, Try converting your word document table to an excel sheet. Please go through the following link. It has enough information to convert word table to excel. http://www.ehow.com/how_2096574_convert-microsoft-word-table-excel.html I have tried converting a word table to excel, it worked for me. Hope this helps...! Thanks Dhanasekaran R

dhana · ‎09-27-2011

Yes I have got O in place of zero. Thanks Ksharp for your idea. But that is not an accepted soultion. . Does some one have any idea ? Thanks Dhanasekaran R

dhana · ‎09-27-2011

You can specify the delete command on the RCMD parameter of the filename statement. FILENAME FILEREF FTP "ABC.DAT" HOST="TESTHOST" USER="USERNAME" PASS="PASSWORD" RCMD="DELE ABC.DAT"; otherwise you can try this also QUOTE RCMD DLTF FILE(mylib/myfile) mylib is the name of the library from which the file is to be deleted. myfile is the name of the file to be deleted. Hope this helps...! Thanks Dhanasekaran R

dhana · ‎09-27-2011

Yes I understood your solution worked well. But I just tried the same output with PROC TABULATE except the 0 with % sign everything is working fine. Can you help how to get this 0 with % sign in my solution. Thanks Dhanasekarna R

dhana · ‎09-26-2011

Thanks to KSharp's idea of using PROC REPORT. Here is one more solution using PROC TABULATE. PROC FORMAT; PICTURE MYPCT LOW-HIGH = '009%)' (PREFIX="("); RUN; OPTIONS MISSING=0; PROC TABULATE DATA = DSN; CLASS RESPONSE CATEGORY; TABLE RESPONSE, CATEGORY*(N='' PCTN<RESPONSE>=' ' *F=MYPCT.); RUN; Though we are getting the expected output, the 0's are not displaying with % sign. Can some one help on how to display 0 along with % sign. Thanks Dhanasekaran R

Online Status	Offline
Date Last Visited	‎07-23-2018 04:32 PM

Re: How to Create Thorn Delimited Text File in SAS

Re: How to Create Thorn Delimited Text File in SAS

Re: How to Create Thorn Delimited Text File in SAS

How to Create Thorn Delimited Text File in SAS

Re: How do I Avoid Duplicates in Merge

How do I Avoid Duplicates in Merge

Re: The symbol is not recognized and will be ignored.

The symbol is not recognized and will be ignored.

Re: PROC SQL and Window Functions

Re: PROC SQL and Window Functions

Re: Deleting external file in FTP...

Re: Reg:Count of obs and month

Re: Data count per minute

Re: macro in a loop

Re: How to loop a Join ? (Outer Union)

Re: Reading comma Delimited File

Re: Macro keyword LET appears as text

Re: Macro keyword LET appears as text

Re: Reading comma Delimited File

Reading comma Delimited File

Re: Macro keyword LET appears as text

Macro keyword LET appears as text

Re: Subseting Teradata table with SAS dataset

Re: Subseting Teradata table with SAS dataset

Subseting Teradata table with SAS dataset

Read a table from Word into SAS

rearranging data in sas based on different response

Re: Deleting external file in FTP...

Re: rearranging data in sas based on different response

Re: rearranging data in sas based on different response