cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
rawindar
‎04-12-2018
  • 70 Posts
  • 0 Likes Given
  • 0 Solutions
  • 0 Likes Received

Re: First 3months transaction amount

by Calcite | Level 5 in SAS Programming
‎05-16-2017 12:58 AM
‎05-16-2017 12:58 AM
Thanks Søren/Naveen/Kurt     Yes it works with both of your queries.Seems to problem with my EG session.It worked in new session.     thank you so much.     Regards, Rawindarreddy ... View more

Re: First 3months transaction amount

by Calcite | Level 5 in SAS Programming
‎05-15-2017 08:31 AM
‎05-15-2017 08:31 AM
Hi Søren/Navin   thanks for immediate response.   I tried both of your queries ,Butunfortunately it did not extract any rows.   Thannk you.   Regards, Rawindarreddy   ... View more

First 3months transaction amount

by Calcite | Level 5 in SAS Programming
‎05-15-2017 07:08 AM
‎05-15-2017 07:08 AM
Hi,   I have below dataset with 4 coloumns.As shown below Account opening date is different to every account. after that he may transact in first 3months from account opening date or may not transact. data account; input account_no $ acc_open_date :date9. trans_date :date9. transamt; format acc_open_date trans_date date9.; datalines; 08660123 02NOV2016 15DEC2016 1000 08660123 02NOV2016 16DEC2016 100 08660123 02NOV2016 19DEC2016 100 08660123 02NOV2016 15JAN2017 1000 08660123 02NOV2016 25JAN2017 1000 08660123 02NOV2016 1FEB2017 1000 08660123 02NOV2016 10FEB2017 1000 08660123 02NOV2016 11FEB2017 1000 08660123 02NOV2016 11MAR2017 1000 08660123 02NOV2016 21MAR2017 1000 08660456 02JUN2016 21OCT2016 1000 08660456 02JUN2016 21NOV2016 1000 08660456 02JUN2016 21DEC2016 1000 ;     here i would need sum(transamt) for the first 3months(if account open month is NOv then trans amount for NOV DEC JAN from the account opeinng date group by account no.   in this case i need 08660123 3200 08660456  0   I tried below query but it's not giving me correct result. proc sql; select account_no,sum(transamt) as totaltransaction from account where month(trans_date) between month(acc_open_date) and month(intnx('month',acc_open_date,2)) group by account_no; quit;   thank you in adavance. ... View more

Re: How to convert char date to date in SQL SAS

by Calcite | Level 5 in SAS Programming
‎05-05-2017 07:38 AM
‎05-05-2017 07:38 AM
data ab; length date1 $10; input date1 $; cards; 05/21/1963 03/20/1967 07/23/1961 08/03/1975 01/24/1960 ; run; data cd; format date2 date9.; set ab; date2=input(date1,mmddyy10.); run; proc sql; create table test as select date1,input(date1,mmddyy10.) as date2 format=mmddyy8. from ab; quit; ... View more

Re: update master dataset with transact datset

by Calcite | Level 5 in SAS Programming
‎04-11-2017 02:31 AM
‎04-11-2017 02:31 AM
Hi   Thank you for your suggestion   the problem here is i have to use all these variables in by statement since it is the primary key and it will get unique values. .date_key bu_key ORD_NUM ORD_LINE_NUM NOTE_NUM.   But when transact getting updated value for examle Note_NUM=52163 where -2 in master datset.   It can not update since im using NOTE_NUM in by variable list.   finally i get two rows in SAS datset as per my code which is having -2 and updated value both   Again if i remove NOTE_NUM in by variable combination i can not meet othet scenarios.   Please suggest how can i replace the entire row which is having -2 in master and coming with updatde value in transact datset based on same combination of by variables.   Thank you in adavance.   Regards, Rawi ... View more

update master dataset with transact datset

by Calcite | Level 5 in SAS Programming
‎04-06-2017 06:49 AM
‎04-06-2017 06:49 AM
data master; format date_key date9.; input DATE_KEY date9. BU_KEY ORD_NUM ORD_LINE_NUM NOTE_NUM; datalines; 11MAR2017 213 304534 1 -2 11MAR2017 213 304534 2 -2 11MAR2017 213 304534 3 -2 11MAR2017 213 304534 4 -2 11MAR2017 213 304534 5 -2 11MAR2017 213 304534 6 -2 11MAR2017 213 304534 7 -2 25Mar2017 226 5466297352 0001 -2 25Mar2017 226 5466292861 2 3.51329E+13 run; proc sort data=master; by date_key bu_key ORD_NUM ORD_LINE_NUM NOTE_NUM; run; data transact; format date_key date9.; input DATE_KEY date9. BU_KEY ORD_NUM ORD_LINE_NUM NOTE_NUM; datalines; 11MAR2017 213 304534 1 928258 11MAR2017 213 304534 2 928258 11MAR2017 213 304534 3 928258 11MAR2017 213 304534 4 928258 11MAR2017 213 304534 5 928258 11MAR2017 213 304534 6 928258 25Mar2017 226 5466297352 0001 35132940826849 25Mar2017 226 5466292861 3 3.51329E+13 run; proc sort data=transact; by date_key bu_key ORD_NUM ORD_LINE_NUM NOTE_NUM; run; data master; merge master(in=a) transact(in=b); by date_key bu_key ORD_NUM ORD_LINE_NUM NOTE_NUM; if a=1 or b=1; run; 　 I have a master datset and transact dataset . Masterdataset have millions of rows at fist comes with note_num=-2 and other xxx values. Next update it comes with other any updated values with note_num=111. for the same ord_num  and other new Ord_nums will come. The problem here is case1:if the same note_num(222) come with updated(222) value(note num same and other values change for remaining coloumns) it will replace in merge.It's fine in this case. case2:if the new ord_num comes then in left join it adds to master.It's fine here. case3.If the note_num come with '-2' new or old it will be ok in merege again. case4:Master dataset contains note_num = -2 for ord_num yyy but the same ord_num(yyy) with same_ord_line_num(1 or 2 or 3) come with new note_num. in this case it should replace -2 with XXX. I can not delete note_num ='-2' from master because few other '-2' rows will be there which is not coming in transact datset. but my final table im getting both -2 and xxx rows.   I need a final o/p as below.   date_key BU_KEY ORD_NUM ORD_LINE_NUM NOTE_NUM 11Mar2017 213 304534 1 928258 11Mar2017 213 304534 2 928258 11Mar2017 213 304534 3 928258 11Mar2017 213 304534 4 928258 11Mar2017 213 304534 5 928258 11Mar2017 213 304534 6 928258 11Mar2017 213 304534 7 -2 25Mar2017 226 5466292861 2 3.51329E+13 25Mar2017 226 5466292861 3 3.51329E+13 25Mar2017 226 5466297352 1 3.51329E+13     thank you in advance.   Regards, Rawi. ... View more

Re: Create a csv file in SFTP server

by Calcite | Level 5 in SAS Programming
‎11-30-2016 07:26 AM
‎11-30-2016 07:26 AM
Hi Kurt, Thans for your quick response.I did not understand this public and private keys.   But i have username and password for this SFTP server.I read that we should not use password for SFTP server so i have run only with username. Please find the below log file   22 GOPTIONS ACCESSIBLE; 23 FILENAME target1 SFTP USER='xx' HOST = 'xx.xx.xxx.com' DEBUG; 24 data _null_; 25 file target1; 26 do i=1 to 10; 27 put i=; 28 end; 29 run; NOTE: Connecting to xxxx-xx.xxt.xx.com... NOTE: The file TARGET1 is: Filename NOTE: ssh: connect to host xxxx.xx.xxt.com port 22: Connection timed out NOTE: 10 records were written to the file TARGET1. The minimum record length was 3. The maximum record length was 4. NOTE: DATA statement used (Total process time): real time 1:03.21 cpu time 0.02 seconds     ... View more

Create a csv file in SFTP server

by Calcite | Level 5 in SAS Programming
‎11-30-2016 07:13 AM
‎11-30-2016 07:13 AM
Hi,   I want to create one of the sas datset in SFTP server in CSV format.I tried below test code to create file in SFTP server,but i could not connect to FTP server.   I also tried to connect to SFTP with libname statement but even that not work.   FILENAME targetSFTP "/xx/test.csv" HOST = 'xx-xx.xx.xx.com' DEBUG; data _null_; file target; do i=1 to 10; put i=; end; run;   Please help me to get it done in two ways with filename and libname.   Thanks in advance   regards, ravi   ... View more

Re: Changing title statement based on condition

by Calcite | Level 5 in SAS Procedures
‎05-26-2014 01:33 AM
‎05-26-2014 01:33 AM
Hi all, Thank you so much for your response.I tried all above,espesially programs by Ksharp and Astounding It was executed successfullz without anz errors,But it did not give the expected resulz.Itäs not able to change the title statement.Could zou guide me more please. Thanks, Rawindarreddy ... View more

Changing title statement based on condition

by Calcite | Level 5 in SAS Procedures
‎05-22-2014 07:02 AM
‎05-22-2014 07:02 AM
Does anzone help me out how to change the title stement in the output window based on condition ex:%global x d;       %let x=ccc;       %let date=23may2014;     data test;      input var1 date;      datalines;      aaa 21may2014      bbb 22may2014      ccc 23may2014      run;      Title "................";      proc print;      run;    Here i need if x=var1 and d=date then title="CCCCCCCCCCCCCCCCCCCCCC"                      if x ne var1 and d=date   then title="BBBBBBBBBBBBBBBBBBBBB"                      if  x ne var1 and d ne date then title="VVVVVVVVVVVVVVVVV"; Thanks in advance.... ... View more

Re: More than one input statements

by Calcite | Level 5 in ODS and Base Reporting
‎06-03-2013 04:56 AM
‎06-03-2013 04:56 AM
Thank you . ... View more

Re: More than one input statements

by Calcite | Level 5 in ODS and Base Reporting
‎05-30-2013 07:40 AM
‎05-30-2013 07:40 AM
You didn't get me i think,I know the answer,i just want to know how it reads data the way of execution. ... View more

More than one input statements

by Calcite | Level 5 in ODS and Base Reporting
‎05-29-2013 02:40 AM
‎05-29-2013 02:40 AM
Hi all, Could any one explain the follwing program,what will be the result of this program and how we get that(the internal process how it reads the data). Thanks. data work.TEST;       drop City;        infile datalines;        input              @1 Name $14.  /              @1 Address $14. /              @1 City $12. ;        if City="New York" then input @1 State $2.;        else input; datalines; Joe Conley 123 Main St Janesville WI Jane Ngyuen 555 Alpha Ave New York NY Jennifer Jason 666 Mt Diablo Eureka CA ; ... View more

Datawarehouse concepts

by Calcite | Level 5 in SAS Programming
‎08-14-2012 09:02 AM
‎08-14-2012 09:02 AM
Hi all,        I have several queries here       1.Datawarehouse contains historical data.If any updations occur in data,how can we update these changes to existed data in datawarehouse.Do we need to write any code.Is the process of applying changes will different to database(oracle..etc) to database(teradata etc).       2.genrally Datawarehouse divides into datamarts.How this will be happen.Is datamart exists in Datawarehouse or will it exists in some other database.Do we need to apply any other process (eg:ETL) to create Datamarts.       3.I have read a statement "While we are loading data into datawarehouse we select data which is relevent and useful to business users".How can we identify this ,Why dont we select whole data.may it contains duplicates.Except this what are the other reasons.        4.What are the differences between ETL and ELT.Which is better one. Thanks & Regards Rawindar ... View more

Re: datawarehouse

by Calcite | Level 5 in SAS Programming
‎08-09-2012 09:05 AM
‎08-09-2012 09:05 AM
i didnt get you.We use datawarehouse to store historical data we can store huge volumes of data.I have read that in DWH once entered data can not be erased.Is it true?. ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎04-12-2018 08:40 AM
Quick links