About peterz

peterz · ‎09-12-2017

Thanks again for your reply Patrick. All good points. I will soon be trying out the DI studio jobs to see how they generate with different table properties. I'll give the views a go too and see how that performs. Thanks.

peterz · ‎09-12-2017

Thanks, This is probably going to be the solution we go for, at least short term. As I mentioned above to Patrick, we have millions of lines of code which need to be checked and changed if needed. Then tested, go through change management and implement. It's a massive job. Thanks for your reply.

peterz · ‎09-12-2017

Thanks for your reply Patrick. The SAS/Access engine to Hive reads the TABLEPROPERTIES element of the DDL to be able to discover the SAS format in which the data should be read/surfaced into SAS. So it is possible. I agree this is normal DB behaviour but if SAS are looking for people to continue to use SAS and not just move wholesale into SQL on DB then they need to provide the fuctionality to enable this. Our system consists of thousands of tables, hundreds of DI batch jobs and millions of lines of user code which interogates the data. I'm just trying to see if there is a simpler way around this than changing all these millions of lines of code and what you say is also the way I read it and is basically that there is no way round it. Thanks agian.

peterz · ‎09-11-2017

Impala stores dates as a datetime. When you write a table to Impala through the Impala engine and that table has a date in it (eg a date9.) Impala stores that date as a datetime and when you then read that table it returns the value as a datetime28.9. So it has not only ignored the format it has completely changed the underlying value from a SAS date value to a SAS datetime value. This makes all exsiting date calculations fail to produce the expected results. As you can imagine if you are moving data and code from a SAS environment to an Impala/SAS environment this 'feature' seems to require a huge amount of recoding to read all dates as a datepart of the datetime or to add the SASDATEFMT= option to every table every time you read it. Does anyone happen to know a simpler solution to this issue because SAS aren't very forthcoming?

peterz · ‎02-13-2014

Try making a format out of your smaller data using proc format and cntlin= option. Then apply that format to your key variables in the big dataset to create the values for the variable you are after.

peterz · ‎11-15-2012

How about: data chars; a='1'; b='2'; c='3'; output; a='11'; b='21'; c='31'; output; run; data _null_; length varnames $1000; set chars (obs=1); array _chars_ _character_; do i = 1 to dim(_chars_); if vname(_chars_) ne 'varnames' then varnames=left(trim(varnames))!!' '!!vname(_chars_); end; call symput('varnames',varnames); run; %put &varnames; %macro char_num(); data nums(drop=c_:); set chars (rename=( %do i=1 %to %sysfunc(countw(&varnames)); %scan(&varnames,&i)=c_%scan(&varnames,&i) %end; )); %do i=1 %to %sysfunc(countw(&varnames)); %scan(&varnames,&i)=input(c_%scan(&varnames,&i),best.); %end; run; %mend char_num; %char_num;

peterz · ‎02-28-2012

Hmmmm, not used to this editor thing. It seems pretty stupid. It has moved all the semi-colons and added carriage returns in random places but the basic syntax you need is there. It also doesn't like me Ctrl+Ving stuff into it.

peterz · ‎02-28-2012

I think you are after: data input_table; infile cards missover dsd; input ID Year Value1 Value2 Value3; cards ; 1,1999,,270 1,1999,,,350 1,1999,,, 1,2000,20 1,2000,,300 1,2000,,,320 1,2001,,122 1,2001,,, 1,2001,,,500 ; 　　 proc means data=input_table noprint nway; class id year; var value1 value2 value3; output out=output_table(drop=_type_ _freq_) max =; quit ;

Online Status	Offline
Date Last Visited	‎04-27-2018 09:45 AM

Re: Impala engine writes/stores dates as datetime. How to get round?

Re: Impala engine writes/stores dates as datetime. How to get round?

Re: Impala engine writes/stores dates as datetime. How to get round?

Impala engine writes/stores dates as datetime. How to get round?

Re: Many to one merge with overlapping variables

Re: Converting all character variables to numeric

Re: How can I combine multiple rows to a single row?

Re: How can I combine multiple rows to a single row?

Re: Impala engine writes/stores dates as datetime. How to get round?

Re: Many to one merge with overlapping variables

Re: Impala engine writes/stores dates as datetime. How to get round?

Re: Impala engine writes/stores dates as datetime. How to get round?

Re: Impala engine writes/stores dates as datetime. How to get round?

Impala engine writes/stores dates as datetime. How to get round?

Re: Many to one merge with overlapping variables

Re: Converting all character variables to numeric

Re: How can I combine multiple rows to a single row?

Re: How can I combine multiple rows to a single row?