About santosh_pat69

santosh_pat69 · ‎06-23-2016

Hi All, Please help achieve the below result I have two tables, Dept1 and details, now i need to create a new table where i need to add the date joining based on the Dept1 column values and the Leave date would be set to a default date of 31Dec9999 for all the columns Emp_Id is missing. Table 1 Details Emp_Id Name Dept Salary Date of Joining LOP Dept1 A001 A Tech 12300 13-Jan-16 14-Jan-16 Tech A002 B Admin 4500 16-Apr-15 17-Apr-15 Admin A003 C Hosp 8970 16-Apr-15 17-Apr-15 Hosp A004 D Tech 8765 2-Apr-15 2-Apr-15 Tech A005 E Tech 43567 9-Apr-15 9-Apr-15 Tech A006 F Hosp 9087 28-Apr-15 12-May-15 Hosp A087 G Admin 9876 20-Jan-13 21-Jun-16 Admin E00057 H Tech 12345 17-Mar-15 19-Mar-15 Tech E0041 I Admin 7645 17-Mar-15 18-Mar-15 Admin E00062 J Tech 89075 14-Aug-14 14-Aug-14 Tech E00015 K Tech 34565 22-May-13 9-Oct-13 Tech E00076 L Hosp 567 20-Jan-13 22-Jun-16 Hosp M M Tech 8970 Tech NN Admin 8765 Admin OO Hosp 43567 Hosp PP Tech 9087 Tech QQ Tech 9876 Tech RR Hosp 7645 Hosp SS Admin 89075 Admin TT Tech 34565 Tech UU Admin 567 Admin VV Tech 7645 Tech WW Tech 89075 Tech XX Hosp 34565 Hosp YY Hosp 89075 Hosp ZZ Hosp 34565 Hosp Table 2: Dept 1 Dept Date of Joining Date of Leave Tech 13-Jan-16 14-Jan-16 Admin 16-Apr-15 17-Apr-15 Hosp 16-Apr-15 17-Apr-15 Tech 2-Apr-15 2-Apr-15 Tech 9-Apr-15 9-Apr-15 Hosp 28-Apr-15 12-May-15 Admin 20-Jan-13 21-Jun-16 Tech 17-Mar-15 19-Mar-15 Admin 17-Mar-15 18-Mar-15 Tech 14-Aug-14 14-Aug-14 Tech 22-May-13 9-Oct-13 Hosp 20-Jan-13 22-Jun-16 Results: Result Emp_Id Name Dept Salary Date of Joining LOP Dept1 A001 A Tech 12300 13-Jan-16 14-Jan-16 Tech A002 B Admin 4500 16-Apr-15 17-Apr-15 Admin A003 C Hosp 8970 16-Apr-15 17-Apr-15 Hosp A004 D Tech 8765 2-Apr-15 2-Apr-15 Tech A005 E Tech 43567 9-Apr-15 9-Apr-15 Tech A006 F Hosp 9087 28-Apr-15 12-May-15 Hosp A087 G Admin 9876 20-Jan-13 21-Jun-16 Admin E00057 H Tech 12345 17-Mar-15 19-Mar-15 Tech E0041 I Admin 7645 17-Mar-15 18-Mar-15 Admin E00062 J Tech 89075 14-Aug-14 14-Aug-14 Tech E00015 K Tech 34565 22-May-13 9-Oct-13 Tech E00076 L Hosp 567 20-Jan-13 22-Jun-16 Hosp M M Tech 8970 13-Jan-16 12-Dec-99 Tech NN Admin 8765 16-Apr-15 12-Dec-99 Admin OO Hosp 43567 16-Apr-15 12-Dec-99 Hosp PP Tech 9087 2-Apr-15 12-Dec-99 Tech QQ Tech 9876 9-Apr-15 12-Dec-99 Tech RR Hosp 7645 28-Apr-15 12-Dec-99 Hosp SS Admin 89075 20-Jan-13 12-Dec-99 Admin TT Tech 34565 17-Mar-15 12-Dec-99 Tech UU Admin 567 20-Jan-13 12-Dec-99 Admin VV Tech 7645 14-Aug-14 12-Dec-99 Tech YY Hosp 89075 20-Jan-13 12-Dec-99 Hosp ZZ Hosp 34565 20-Jan-13 12-Dec-99 Hosp

santosh_pat69 · ‎06-03-2016

Hi, Thank you!!! I was not able to remember this.

santosh_pat69 · ‎06-03-2016

Hi Experts, I am trying to extract the list of SAS program names from a dataset, i have hundreds of them and the special chars are present at for example /xxxx/abcde/abcdefgh.sas /xxxx/abcde/lkml/abcdefguh.sas /xxxx/abcde/lkml555/abguh.sas I am trying to get the output as abcdefgh.sas abcdefguh.sas abguh.sas

santosh_pat69 · ‎05-26-2016

Hi , Thank you !!! I was able to do it by using the below, libname myxls xlsx "D:/.....xlsx"; data csvdata(Drop=Len); Length newvar $50.; set myxls.sheet1; newvar=put(ID, 16.); Len=length(newvar); Rename Newvar=ID; run;

santosh_pat69 · ‎05-26-2016

Hi, I am trying to import an excel(.xls,.xlsx,csv) one of the columns has more than 20 length long values, and after the import the data is displayed as Values on the Excel: 100000000882336 Displayed value: 1E14 1.0000001E14 can you please suggest?

santosh_pat69 · ‎05-25-2016

Hi Reeza, Thank you !! I have created a dataset.

santosh_pat69 · ‎05-25-2016

Hi Reeza, Thank you !!! i need it ina dataset, i have tried outputting the Report created but not getting the desired report.

santosh_pat69 · ‎05-25-2016

Hi, I am trying to create One to Many relation ship, i have a dataset which has similar to a school table as shown below I ned to list out the distinct Class along with other values Input DataSet Name Class Subject Address xxxx 11 PCMB dsdsdf,ewhejwe xxxxsder 11 PCMB ASDERTG,Fretr Asdertrtg 10 ABCDER 123456789 DFERTRYO 8 PCMB Oierl saujs rtieutrttrt 9 PCMB Olkirta Ikitherr 9 ABCDER IKIJHSS Wfdfdere 8 PCMB OIUREERR WERTGGFDD 11 PCMB OLKIERRE Output Expected: Class Name Subject Address 11 xxxx PCMB dsdsdf,ewhejwe xxxxsder PCMB ASDERTG,Fretr WERTGGFDD PCMB OLKIERRE 10 Asdertrtg ABCDER 123456789 9 rtieutrttrt PCMB Olkirta Ikitherr ABCDER IKIJHSS 8 DFERTRYO PCMB Oierl saujs Wfdfdere PCMB OIUREERR I tried using the Group by, Retain and other Stat procedures like Proc Summary, Freq was not able to acheive the result. Kindly Suggest me on this. Thanks

santosh_pat69 · ‎05-25-2016

libname xl Excel "&SASForum.\Datasets\Append.xls"; /* Create the Append.xls workbook with a sheet named myData$ and a named range called myData */ data xl.myData; x = 1; run; %macro append; %do i = 2 %to 5; data have&i; x = &i; run; proc sql; /* Save the contents of the Excel table */ create table have as select * from xl.myData; /* Erase the contents of the table in the workbook, Excel doesn't allow you to overwrite an existing table */ drop table xl.myData; /* Recreate the table with your added lines. It will replace the old version at the same place, with the same cell formatting */ create table xl.myData as select * from have union all select * from have&i; quit; %end; %mend append; %append; libname xl clear;

santosh_pat69 · ‎05-09-2016

Hi The data is extracted from a teradata table, there a jobs scheduled. the RDBMS jobs keep executing, periodically and the tables are updated as and when the data is avaialble. the dataset which we are using to generate the report is most of the times updated by 14.00, and if the reports dosent contains the data for the date we are looking for, we check it in a couple of hours manually the data will be availabe by that time. and we re-generate the report once the data is loaded. kindly suggest.

santosh_pat69 · ‎05-09-2016

Hi Reeza, we have field called CNTRL_DTE i have tried using this %let Tdy= 01012016; /*Some dummy date used here*/ If cntrl_Dte= "&tdy." and obs = 0 then do; put Note:"Check the table for updates after sometime"; End; Else Do; File email EM; End; Run; for some reason this is not working sometimes this sends the email for the updates and some times hte log says this condition If cntrl_Dte= &tdy. and obs = 0 is false;

santosh_pat69 · ‎05-09-2016

Hi, I am trying to check if the table is updated for a particular date. Once the data is available for the mentioned date an email need to triggered to a list of users, else need to check after a couple of hours. Please help!!!!

santosh_pat69 · ‎04-26-2016

Hi Super User, Thank you so much!!!! Your comments and suggestions helped me alot to achieve.

santosh_pat69 · ‎04-26-2016

Hi Super user, Thank you for your comments and suggestions. FIND or index need to be used, i was looking for a function which search the position of "&=,?" and read it into a macro variable and then use the macro variable in a Do loop other than creating the other variables based on the position of the & = , ?. Kindly suggest

santosh_pat69 · ‎04-26-2016

Hi Thank you for looking into this and posting your vluable comments. How do you know which variables are character and which are numeric? For example, Phone Number would normally be stored as character even if it contains only digits. The variables stored as character. Should each observation in the data set execute only the VAR_A instructions that appear on that observation? Or does the contents of VAR_A apply to all observations in the data set? VAR_A apply to all observations in the data set. Could variables have different lengths for different values of VAR_A? For example, could one observation contain &STATE=Utah& while another contains &STATE=Minnesota& and another contains &STATE=New York& ? Do you know what the longest required length would be for each possible character variable referenced within VAR_A? Yes it can have multiple lengths, i am not sure of the longest length as the VAR_A looks something like this &STATE=Minnesota&PRODUCT=AAAXXXCCC&TELEPHONE=1234567890. so i have defined length of A as 250.

Online Status	Offline
Date Last Visited	‎03-02-2017 02:56 PM

Re: need to create a Control table using which the file should be load...

need to create a Control table using which the file should be loaded t...

Re: Create a Pivot table using BASE SAS

Re: Create a Pivot table using BASE SAS

Re: Create a Pivot table using BASE SAS

Create a Pivot table using BASE SAS

Re: Pivot table Using SAS

Re: extract from teradata

Pivot table Using SAS

Re: Conditionally Email

Re: Stored Process for MS Excel

Re: To continue running schedule jobs when teh Workspace server space ...

Re: To continue running schedule jobs when teh Workspace server space ...

Re: Help in Creating Oracle library

to merge two datasets

Re: Need to extract a char between special char from a string

Need to extract a char between special char from a string

Re: SAS unable to read long value numeric( displaying as exponential)

SAS unable to read long value numeric( displaying as exponential)

Re: need to create a One to many relationship

Re: need to create a One to many relationship

need to create a One to many relationship

Re: ODS Excel APPEND

Re: How to check if a dataset is updated for a particular date

Re: ned to check if the dataset is updated for a particular date

How to check if a dataset is updated for a particular date

Re: to create new variables from exsting variable which is delimited

Re: to create new variables from exsting variable which is delimited

Re: to create new variables from exsting variable which is delimited