About hari1109

hari1109 · ‎08-29-2012

Hi patrick, I think i need to edit the odbc.ini file(UNIX platform) in this case if i have to set readbuff option to 1500. is that right. Thank you, Harish.

hari1109 · ‎08-29-2012

Hi All, i have been trying pull information from a table. If i limit the no of rows to 1500 then i am able to pull the data successfully buy i have to apply a filter condition on the database table. The below is the error i am receiving while i attempt the get the records from the database table. I am using Unix SAS and am pulling the information from SQLSVR database table. The Error I am receiving : ERROR: CLI cursor extended fetch error: Unable to retrieve error message. The below is one of the methods i used to pull the information as i am not able to pull more than 1500 records at a time. proc sql ; select count(*) into : count from emprd.serviceauth; quit; %put &count; %macro test(finish); %let i=1; %let obs = 1; %let obs_1 = %eval(&obs+1500); %do %while(&i < %eval(&finish/1500)+1); proc sql ; create table final_&i as select diagnosisnarrative from emprd.serviceauth(firstobs = &obs obs = &obs_1) where id in (select id from final_6); quit; data final; set final final_&i; run; %let i=%eval(&i+1); %let obs = %eval(&obs+1500); %let obs_1 = %eval(&obs_1 + 1500); %end; %mend test; %test(&count) I came across another method which is to use a macro, explained in the below paper : http://www.lexjansen.com/wuss/2011/posters/Papers_Bettinger_R_74821.pdf %SQZ_LIBRARY( LIBNAME /* name of SAS library to squeeze */ , EXCLUDE= /* List of datasets to EXclude */ , INCLUDE= /* List of datasets to INclude */ , LIST=N /* Flag for listing of dataset contents */ , TRIALRUN=Y /* Flag for mode of operation */ ) There is no macro code available like the one we have for %Squeeze macro. Can any one explain how to use this macro to squeeze my SQLSVR database table emprd.serviceauth. Thank you, Harish.

hari1109 · ‎05-24-2012

Hi Tom / ballarw, No i don't need the extra underscore , yes i would like to use it as a filename and no, i didn't included the entire file path sorry for that. Yes ballardw , sure i will do that and yes i trimmed a lot but i am getting the output with original code. Thank you, Harish.

hari1109 · ‎05-23-2012

Got solved but i don't know the reason : Changed %macro report(); to %macro report; and it worked. Thank you, Harish.

hari1109 · ‎05-23-2012

Hi all, Can anyone tell me where i am going wrong? I tried to rectify the mistake but i am not able to do so. options symbolgen mlogic; %macro scp; %global lob1 lob2 b; %let lob1 = XAB; %let lob2 = BAS; %do l = 1 %to 2; %let b = &&lob&l; %macro report(); ods listing close; ods html file = "/rpt6_&b_..xls"; data empty4; msg = "welcome"; run; %mend report; %report %end; %mend scp; %scp Not able to resolve macro variable b for the first iteration and for the second iteration it is showing the value of lob2 which is BAS. I sahould have value XAB for first iteration and BAS for second iteration and i am not able to resolve macro variable b for the first iteration. Thank you, Harish.

hari1109 · ‎05-16-2012

Hi Fried Egg, Thank you for the reply and i have tried with the second libname statement and i am still not able to assign the libname statement. I have my server name MSSQL09, database name ,schema name, user name and password , i am trying to connect using the libname statement in SAS. Thank you for that INFO, i will look into it. Thank you, Harish.

hari1109 · ‎05-16-2012

Hi All, Thanks in advance, I have been trying to connect to a database on MSSQL server and i am facing trouble with the libname statement that i have to use. I have the user name and password to access the server but i am not able to run the libname statement. i am using the following libname statement. libname mssql user = password = database = server = schema = ; i am receiving that the libname statement is not assigned. Thank you, Harish.

hari1109 · ‎03-07-2012

Hi ART, I have gone through those and are helpful but my issue is different , here i have to add an excel sheet every month and i have to put that sheet in the exisitng excel file. I have an excelfile with one sheet from the previous month and now i would like to add another sheet as sheet 2 to the existing excel workbook. In the same way i will be adding sheets as (sheet 3 ,sheet 4,...,sheet12 )to the existing excel workbook month after month. one way i am trying to do is to direct all the data sets to a location and then using proc datasets procedure to copy all the datasets from the pervious and current month to the work library and then using ods tagsets to create mutiple sheets in single excel work book. Thank you.

hari1109 · ‎03-06-2012

Hi all, I have a sheet in an excel work book and i would like to add another sheet as sheet 2 to the existing workbook at some point of time later (may be a month after) and in such a way i will be adding sheets generated using Proc report to the existing workbook. Is there any way to achieve it. Here i don't have a specific number of sheets in hand to write them off to the single workbook, i will be running my code to generate (using ODS TAGSETS.msoffice2k ) a new sheet every time and i have to add this new sheet to the existing workbook which may or may not be having sheets in it. Thank you all.

hari1109 · ‎02-22-2012

Thank you for your help, Tom and art297 , i could have wasted a lot of time without knowing what i need do.

hari1109 · ‎02-20-2012

Hi art297 No, They are numeric values.Thank you

hari1109 · ‎02-20-2012

Hi TOM, I tried with the above code and i am getting this error : ERROR: Expression using addition + requires numeric types. CASE WHEN((C.PLN_01 = '90') OR (C.PEN_01 IN ('61','62','65','66','68','69','A7','B6') AND C.PEN_01 NOT IN ('27','33','34','35','42','43'))) THEN CALCULATED TBL_01 + D.ICT_01 ELSE CALCULATED TBL_01 + D.ICT_01 END AS TBL_01 Here TBL_01 is the new variable i am calculating. Thank you.

hari1109 · ‎02-20-2012

Thank you, Sorry I haven't mentioned enough here, ok suppose if i have records between some dates let it be 01JAN004 AND 31JUN2004 from the table GE. since I am using a by variable CLE_DT( which is a date in a given month, in this case i will be have 6 such dates 31JAN2004,29FEB2004,31MAR2004,...,30JUN2004) and I will be having only 6 records with variables CLE_DT,TBL_01,TBL_04 AND CODE( which is always GE in this case). I would like to get only 6 records out of proc sql instead of many number of records. For that i have to find an equivalent of if/else and if statements in proc sql and since i can't use a by variable in proc sql i can use a group by on CLE_DT and at the same time i should be able to calculate new variables(TBL_01,TBL_04) in proc sql as it is in the datastep. I can provide the code it looks the same to that of what i have provided earlier. Thank you.

hari1109 · ‎02-20-2012

Hi all, Can anyone let me know a better approach where i can be able to include all the if else and if statements and where i can do some summations on some newly generated variables in proc sql. Here is what i am doing (please look at the code i have attached) I am pulling some records from tables using proc sql and in the data step i am doing the summations in the if else and if statements and at the same time i am using a by variable(CLE_DT). My issue is, to pull the records itself it is taking a long time and i would like to improve the performance by including all the if else and if statements and do some summations and then group by in the proc sql itself. I have gone through some papers where we can use some case statements and nested case statements but in this case i am caluculating two new variables(TBL_01, TBL_04) based on conditons and at the same i am using a by variable.So,kindly suggest a better approach. Thank you all.

hari1109 · ‎02-01-2012

Like in cobol i have to read a record from the dataset(let it be dataset = test) and do some maniplations and if it meets the requirment then write the record to the new dataset (let it be test1) and then using proc sql the record obtained from new dataset (test1) will be inserted into another dataset (test2) . Again if i have go to statements in the later part of the program , i might need to read the next record from the dataset test, i mean the second but not the first record from the dataset test and in a similar way if it pass through the conditions then dataset (test1) will have this record and will be inserted into dataset test2. So, Initially i am reading only first record from dataset test. Hope my issue is clear. Thak you.

Online Status	Offline
Date Last Visited	‎09-01-2015 07:11 AM

Re: %SQZ_LIBRARY Macro

%SQZ_LIBRARY Macro

Re: Not able to resolve macro within %do loop.

Re: Not able to resolve macro within %do loop.

Not able to resolve macro within %do loop.

Re: MSSQL LIBNAME

MSSQL LIBNAME

Mutiple Excel files into a single excel workbook

Mutiple Excel files into a single excel workbook

Re: IF/ELSE AND IF STATEMENTS,GROUP BY IN PROC SQL

IF/ELSE AND IF STATEMENTS,GROUP BY IN PROC SQL

Re: %SQZ_LIBRARY Macro

%SQZ_LIBRARY Macro

Re: Not able to resolve macro within %do loop.

Re: Not able to resolve macro within %do loop.

Not able to resolve macro within %do loop.

Re: MSSQL LIBNAME

MSSQL LIBNAME

Mutiple Excel files into a single excel workbook

Mutiple Excel files into a single excel workbook

Re: IF/ELSE AND IF STATEMENTS,GROUP BY IN PROC SQL

Re: IF/ELSE AND IF STATEMENTS,GROUP BY IN PROC SQL

Re: IF/ELSE AND IF STATEMENTS,GROUP BY IN PROC SQL

IF/ELSE AND IF STATEMENTS,GROUP BY IN PROC SQL

IF/ELSE AND IF STATEMENTS,GROUP BY IN PROC SQL

Sasdataset