About rajeshm

rajeshm · ‎09-17-2020

%let pre_first_date = %str(%')%sysfunc(putn(%sysfunc(intnx(month, %sysfunc(today()), -1, begin)), date9.))%str(%'); %let pre_last_date = %str(%')%sysfunc(putn(%sysfunc(intnx(month, %sysfunc(today()), -1, end)), date9.))%str(%'); %let d_pre_first_date =%str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1)),date9.))%str(%')d; %let d_pre_last_date =%str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1,end)),date9.))%str(%')d; %put &pre_first_date &d_pre_last_date; proc sql; create table xxx as select zz from mytablexxx /* option1 where po_date between &d_pre_first_date and &d_pre_last_date;--not working*/ /*option2 where po_date between "&d_pre_first_date." and "&d_pre_last_date.";ERROR: Expression using IN has components that are of different data types.*/ where po_date between "&pre_first_date."d and "&pre_last_date."d;/* working fine */ quit; I am so sorry for posting partial data in my earlier mail,plsconsider that let me know why first and second options are not working.

rajeshm · ‎09-13-2020

Thanks for the answer. i did not notice csv and xlsx are different formats with the code. explanation from my end: different programs using different ways and finally exporting. I opened these output files and noticed differences and myself blindly assumed this is happening bcaz of code but finally concluded xlsx and csv are different and thats why i am getting different results. csv exports including formats ? xlsx willl interpret the dataset values ( i dont know how) and will display the result, is it?

rajeshm · ‎09-11-2020

/* dttimevar --datetime20. */ data xx; set yy; dttimevar dtdate9.; run; /*when exporting above xx as csv,getting date in required format(11aug20)*/ /*proc export data=xx; outfile="/home/fk023682/outputs/first.csv" dbms=csv replace; run;*/ proc sql; create table xx as select dttimevar format=dtdate9. from yy; run; /* after exporting dataset as xlsx,getting date in long numer(1911945600), what should i do? 1.Same formats used but why export is giving different results in xlsx and csv?

rajeshm · ‎09-11-2020

without ods and with proc export , can i solve my issue or not?

rajeshm · ‎09-11-2020

proc export data=xx outfile="/bbb.xlsx" dbms=xlsx replace; sheet="firstl";

rajeshm · ‎09-11-2020

you have given correct solution but i am merging. i posted a new question/thread. thanks for your help.

rajeshm · ‎09-11-2020

mmdd2 is datetime20. informat 1. I would like to get the date from that variable, so used dtdtate9. to read date from datetime20... dataset field value showing correct(dateformat). but while exporting xlsx getting big number , how to get date in excel for mmdd2 ,please let me know MMDD2 format=dtdate9.,

rajeshm · ‎09-11-2020

/* MMDD1,2,3 are datetime20. format in tab dataset 02AUG2020:00:00:00*/ proc sql ; create table Deposits_DetailXX as select MMDD1, MMDD2 format=dtdate9., datepart(MMDD3) format=date9. from tab; quit; tab dataset opened MMDD1 MMDD2 _TEMA001 02AUG2020:00:00:00 02AUG2020 02AUG2020 exported as xlsx MMDD1 MMDD2 _TEMA001 8/2/2020 12:00:00 AM 1911945600 2-Aug-20 1. where is MMDD3 varible? is it renamed to _tema001 ? why and how? 2. i used somewhere this format dtdate9. got expected result. but i am not getting how to use now.. 3. date9. can't applied on datetime varible ,so datepart is calculated and then applied date9. and it is working fine.

rajeshm · ‎09-11-2020

/* mmdd2 02AUG2020:00:00:00 datetime20. in sas dataset tab */ proc sql ; create table Deposits_DetailXX as select MMDD2 format=dtdate9., from tab; quit; when i exported deposits_detailxx , getting mdd2 as largenumber. what should i do to get date9. format? /* datepart function with date9. is working fine. but would like to know otherways. */

rajeshm · ‎09-10-2020

ods listing close; ods results off; ods csv file='/xxxx/Aug.csv' ; proc print data=final noobs; format xxdate mmddyy10.; run; ods csv close; ods results on; ods listing; xxdate field inside sas is 02JAN2019:00:00:00(field is datetime20 format and informat) ods with csv sheet should contain 02JAN2019

rajeshm · ‎09-02-2020

Thanks. I got clarity with the below statement. "Setting the infornat in a SQL SELECT does in fact do nothing, informats come only into play when character data is converted to numbers, either in an INPUT function or an INPUT statement."

rajeshm · ‎09-01-2020

I am confused. Round function is to round the value in sas but while reading data from dwh and clearly indicating to read (with informat 11.2) it should read upto 2 decimal values ex: 143.33 why it is not matching with other datasets 143.33?? i did not use the round function. please explain why informat is not working here? May be round function is solution but i would like to know importance of informat here..

rajeshm · ‎09-01-2020

Agreed. format is for presenting the data and informat is for reading the data, means that i told sas to read in that way. when i checked my data also it showed me 143.24 like that. but later problem started. proc sql; create table mytable as select f.TRANSACTION_AMT as sumamount format=11.2 informat=11.2 length=8, from xxxxxx quit; Even when i checked the data it showed me 143.24 in sas but finally faced problems with this kind of issues(143.24!=143.24) .thats why i would like to know. my surprise is for few records it matched but for problem.i confused a lot. Dont mind for my stupid questions but what i read about informat is not matching here.

rajeshm · ‎08-27-2020

How can i achieve Datasets "more_in_a and "more_in_b" with proc sql? Datasets "Match,only_a,only_b" can be achieved with proc sql.

rajeshm · ‎08-27-2020

while reading from database into sas , I put length and format+informat (format+informat 11.2, length 😎 proc sql; select AMT as sumamount format=11.2 informat=11.2 length=8, from mytable f where xx condition; quit; it should read from database as 11.2 informat only right ? but after reading into sas. 234.44 is not equal to 233.44 (pulled from another dwh) this is happening for few records only. Thanks in advance

Online Status	Offline
Date Last Visited	‎04-07-2023 11:04 AM

Re: Zipping xlsx files

Re: list input missing varible values reason2

Re: list input missing varible values reason2

Re: list input missing varible values reason2

Re: list input missing varible values reason2

Re: list input missing varible values reason2

Re: list input missing varible values reason2

Re: list input missing varible values reason

list input missing varible values reason2

Re: list input missing varible values reason

Re: Transpose two variables at the same time

Re: list input missing varible values reason

Re: syntax highlight steps within macros and %do loops

Re: FORMATTING DIFFERENCES WITH proc sql vs datastep

Re: dataset differences //Both,exclusiveA,exclusiveB

macro date

Re: FORMATTING DIFFERENCES WITH proc sql vs datastep

FORMATTING DIFFERENCES WITH proc sql vs datastep

Re: datetime20. format

Re: datetime20. format

Re: date from datetime with proc ods

Re: datetime20. format

datetime20. format

Re: date from datetime with proc ods

date from datetime with proc ods

Re: dataset differences //Both,exclusiveA,exclusiveB

Re: dataset differences //Both,exclusiveA,exclusiveB

Re: dataset differences //Both,exclusiveA,exclusiveB

Re: identical records between 2 datasets

Re: dataset differences //Both,exclusiveA,exclusiveB