About metallon

metallon · ‎05-19-2016

Hello SAS Experts, I get data via an XLSX import that contains the HEX symbol A0 or  , it looks like a space, but istn. How can I remove this "fake" space? So far this did not work: proc sql; CREATE TABLE _testtest AS SELECT TRANWRD (CN,' ',''), COMPRESS (CN), CN FROM xxx; QUIT; Bye John

metallon · ‎05-03-2016

Hi SAS Experts, I looked at the round function explanation and read that a ,1 should result in no decimals but my calculation reads 100,0 now and not just 100. proc sql ; CREATE TABLE mytable AS SELECT ROUND((SUM(numberA)*100)/SUM(numberB),1) AS percent_ab FROM aaaa; QUIT;

metallon · ‎05-02-2016

Hi SAS Experts, I need to create an XML file that has a unique ID every time I generte it. The data fo create the XML is extracted from a sas data set using proc sql and now I wonder how to generate a unique timestamp, preferable something just numbers. proc sql; create table VDR as select %sysfunc(datetime(),datetime18.) from sashelp.class; run; Any ideas?

metallon · ‎04-21-2016

Hi SAS Experts, I cant get my head around it. I need to mark the older animal within an animal-group with a higher Size as 'x' but in case the younger animal has a higher Size value with a 'y' Animal Age Size 1 13 18 1 7 20 2 6 17 2 2 18 3 5 17 3 3 25 Any ideas? Preferable using proc sql. I think in Oracle this would work with partitioning the data set.

metallon · ‎04-21-2016

Still not working. Its a character variable, length 21. I importet the data from a .xls the attached screen print shows the data. if I copy and paste it I can detect the spaces/blanks. and If I go over the data set with a proc sql and compress its not working. proc import datafile= '....' dbms=xls out=yyyyy replace; *overwrites an existing SAS data set.; getnames=no; datarow=2; run; proc sql; CREATE TABLE xxxx AS SELECT compress(B,,'s') AS A /*not working*/ from yyyyy; quit; /*further test*/ proc sql; CREATE TABLE xxxx AS SELECT compress('0802 12',,'s') AS A /*not working*/ ,compress('0806 20',,'s') AS B /*not working*/ ,compress('2008 11 10',,'s') AS C /*works*/ from sashelp.class; quit;

metallon · ‎04-21-2016

odd. I go to a colleague now and check it at his PC. its a space, see for yourself: proc sql; CREATE TABLE xxxx AS SELECT COMPRESS('0709 30 00',,'s') AS CNA /*not working*/ ,COMPRESS('ex 1211 90 86',,'s') AS CNB /*not working*/ ,COMPRESS('0802 11',,'s') AS CNC /*not working*/ ,COMPRESS('0802 12',,'s') AS CND /*works*/ ,compress('ex 1211 90 86',,'s') AS CNE from sashelp.class; quit;

metallon · ‎04-21-2016

Hi dsbihill, Thanks for the hint. But that did not do the trick. @Loko, thanks for the example. That worked in this particular case but in no other 🙂 proc sql; CREATE TABLE xxxx AS SELECT ,COMPRESS('0709 30 00',,'s') AS CNA /*does not work*/ ,COMPRESS('ex 1211 90 86',,'s') AS CNB /*does not work*/ ,COMPRESS('0802 11',,'s') AS CNC /*does not work*/ ,COMPRESS('0802 12',,'s') AS CND /*works*/ from yyyy; quit;

metallon · ‎04-20-2016

Hello, any ideas why compress is not getting rid of the space in the middle? proc sql; CREATE TABLE xxx AS SELECT A AS CommDesc ,COMPRESS('0802 12','') AS CN from yyy; run; Thanks

metallon · ‎04-13-2016

Hello SAS Experts, Is there a way to read the file name acutally processed and put it in the first column of the dataset? data Import_Import; infile datalines; length fil2read $40; input fil2read $; infile dummy filevar=fil2read end=done DSD DLM=';'; do while(not done); INPUT A : $CHAR11. B : $CHAR11. C : $CHAR11. D : $CHAR100. E : $CHAR96. F : $CHAR1. G : $CHAR6. H : $CHAR2. I : ?? COMMAX4. J : $CHAR11. K : $CHAR2. L : ?? YYMMDD10. M : $CHAR17. N : $CHAR4. O : $CHAR1. P : $CHAR4. Q : $CHAR1. R : $CHAR1. S : $CHAR4. T : $CHAR1. U : $CHAR1. V : $CHAR1. W : $CHAR1. X : $CHAR4. Y : $CHAR1. Z : $CHAR1. ; output; end; datalines; 'C:\Users\x\test\H20154.csv' 'C:\Users\x\test\H20154.csv' 'C:\Users\x\test\B20154.csv' 'C:\Users\x\test\H20154.csv' 'C:\Users\x\test\N20154.csv' ;

metallon · ‎04-13-2016

Hi, I tried to do only this small step and receive the warning "NOTE 485-185: Informat YYMMDDD was not found or could not be loaded." The column entry_date_new is empty. 62 63 data H_finished_new; 64 set work.H_finished; 65 66 entry_date_new=input(entry_date,yymmddd10.); __________ 485 NOTE 485-185: Informat YYMMDDD was not found or could not be loaded. 67 68 run;

metallon · ‎04-12-2016

anyway. even if I use your suggested format it does not work: data want; char_date="2015-01-01"; entry_date=input(char_date,yymmdd10.); format entry_date date9.; run; turns out to be 01JAN2015 and I need 2015-01-01. when I use data H_finished; set work.finished; entry_date=input(entry_date,yymmdd10.); format entry_date date9.; run; the date still turns out as ' 203'

metallon · ‎04-12-2016

YYMMDD10 has 2 digits for year E8601DNw. has 4 digits for year, like I do e.g. 2015

metallon · ‎04-12-2016

Hi RW9 A character date like '2015-10-01' becomes ' 203' data H_finished; set work.finished; entry_date=input(entry_date,E8601DNw.); format entry_date date9.; run;

metallon · ‎04-11-2016

Hi SAS Experts, I try to convert one of my character columns into a date one. The following statement works fine but.. data H_finished; set work.finished; format entry_date date9.; run; problem : The column type remains character. If I use the format I acutally want E8601DNw. I get the error: "An operation that opens a SAS® data set in SAS® Enterprise Guide® fails with the error "Format names must start with an English letter" The dates are already correctly formatted yyyy-mm-dd just as a character.

metallon · ‎04-06-2016

8-16 files. OK I am convinced. I tell the guys responsible that they need to get the MS Excel formats right 🙂 Thanks

Online Status	Offline
Date Last Visited	‎08-01-2016 06:01 AM

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

PROC SQL conditional JOIN with OR operator or CASE WHEN

Re: updating datasets online via UI

Re: updating datasets online via UI

updating datasets online via UI

Re: "ERROR: The user does not have sufficient authority to access the ...

"ERROR: The user does not have sufficient authority to access the data...

Re: SAS DI Studio: Adding STP to Job (grayed out)

Re: PROC SQL conditional JOIN with OR operator or CASE WHEN

Re: updating datasets online via UI

Re: count distinct, group by in WRS

Re: SAS DI Studio: Adding STP to Job (grayed out)

Re: proc sql to retrieve 10m rows from Oracle does not finish

Re: Oracle Stored Procedure DBMS_OUT in SAS

Remove spaces type characters from string

proc sql rounding percentage no decimals

proc sql generate timestamp

analysing observations within an observation group, pref. proc sql

Re: COMPRESS function not working

Re: COMPRESS function not working

Re: COMPRESS function not working

COMPRESS function not working

INFILE multiple files and file name into data set

Re: How to convert a character variable to a numeric date type variabl...

Re: How to convert a character variable to a numeric date type variabl...

Re: How to convert a character variable to a numeric date type variabl...

Re: How to convert a character variable to a numeric date type variabl...

How to convert a character variable to a numeric date type variable

Re: Bulk MS Excel Import fails with multiple files