About marieK

marieK · ‎05-12-2020

Thank you very much! The NUMXw. was exactly what i needed!

marieK · ‎05-11-2020

Hi everybody, I would like to read a CSV file into SAS (SAS 9.4) which has commas as decimal points. The number of decimal places are different. Here is an example of my csv file: ID pop 5119 77,09 5120 74,52 5122 89,54 5124 168,39 5154 1233 5158 407,21 5162 576,42 5166 563,2 5170 1042,81 I am looking for a format like BEST12. but using commas instead of periods as decimal points. Does that exist? If not, is there another easy way to read these numbers properly without transforming the numbers to character variables and exchange "," to "."? (I have 200 of these variables, so i need something more "practical"). Thank you very much in advance!

marieK · ‎08-11-2017

The solution is very simple 😉 It seems that the viewer to look at the Oracle datatables in SAS has a limit of 1024 characters for text variables in Oracle. As soon as I look at the data with another tool (sqldeveloper) I can see all values without truncation. If I read the Oracle dataset back into SAS with the DBMAX_TEXT=8000, I can also see the untruncated values in the work SAS dataset test3. data test3; set xxx.test1(DBMAX_TEXT=8000); run;

marieK · ‎08-10-2017

Thank you very much for your answer. Unfortunately it is not working out as well... I found out, that the variable is already 1024 characters long after I added it with the alter table add DIAGS CLOB. So even before I insert the values.

marieK · ‎08-10-2017

Hi, I have a SAS dataset with a text-variable (diags, 8000 characters) which I need to insert into an Oracle database table. Unfortunately the length of the variable is always truncated to 1024 characters, even with DBMAX_TEXT=8000 and CLOB datatype. I don´t get any error messages or warnings in the log. Do you have any ideas what could be the problem here? Thank you very much! I use the following code with SAS 9.3: libname _d2s_lib oracle user=xxx password="xxx" path=xxx schema=xxx DBMAX_TEXT=8000; proc sql; connect to oracle (user=xxx password="xxx" path=phdb2b DBMAX_TEXT=8000); execute ( alter table xxx.test1 add ("IDNO" number, "SEX" number, "AGE" number, "DIAGS" CLOB) ) by oracle; disconnect from oracle; quit; proc sql; insert into xxx.test1 select * from work.test; quit;

marieK · ‎09-06-2013

Hi Linus, thanks! I will try my luck there

marieK · ‎09-06-2013

Hallo, I would like to improve the run time of my SAS program. For that I am tryig to translate my SAS code into SQL code. Does anyone knows if there is an aquivalent procedure in SQL for the SAS function compress()? I would like to extract specified ICD codes from a reference list and want eliminiate special characters from the codes (+-!.#*). see code below: PROC SQL; connect to oracle (user=&user. password="&password." path=&path.); create table test as select ID, INP_ID, DIAG from connection to oracle (select ID, INP_ID, DIAG from lib.table_hosp) where (upcase(strip(compress(DIAG,"+-!.#*"))) in (select upcase(strip(compress(ICD_10,"+-!.#*"))) from ref.icd)); disconnect from oracle; QUIT; to improve the run time I would like to perform the query directly in oracle like: PROC SQL; connect to oracle (user=&user. password="&password." path=&path.); execute( create table test as select ID, INP_ID, DIAG from lib.table_hosp where (TRIM(upper(REGEXP_REPLACE(DIAG,'[-!.#*+]',''))) in (select TRIM(upper (REGEXP_REPLACE(ICD,'[-!.#*+]',''))) from ref.icd) and IDNUM in(select PATIENTID from safeguard.Patients_out_dus_cov_mn)) ) by oracle; disconnect from oracle; QUIT; I tried 1000 of possibilities and googled around and found the procedure REGEXP_REPLACE() but nothing works out. Do you have any idea? Thanks in advance! Marie

marieK · ‎09-16-2011

thanks for your answers..our problem has been solved from alone over night. it might have been just a server-problem?! runtime is now the same with and without macro.

marieK · ‎09-15-2011

you are my hero works fine! but it also takes sooo long.ggrrr. is it because of the macro?

marieK · ‎09-15-2011

uuhuuhuh looks scary i willl try!! thank you soo much!

marieK · ‎09-15-2011

Thanks for your answer but the SQL IN-function cant handle the %-sign..:( this i tried too

marieK · ‎09-15-2011

Thank you very much for your help. i already tried this version but then i have runtime-problem with 6 million patients because the sql-statement also need to do another select statement (has to select the sample-patientes from a big database): at the moment the macro looks like: %Macro test (dx=); proc sql; create table diag_heart as select patid, diag; from table1 where patid in (select patid from sample); quit; data diag_heart; set diag_heart; if diag IN:(&dx); run; %Mend test; %test(dx=('I05%','I06%','I07%')); %test(dx=('G051%')); This macro is exactly doing what i want but really takes to long. If i dont write it as macro and put all together into a sql-statement (like you told above with the like statement) it is a lot faster.. but i need a macro :smileygrin: do you see a possibility to combine these 2 steps?

marieK · ‎09-15-2011

Hey Guys, i have a problem in Proc SQL. I would like to compare a Variable with diagnoses with a list of ICD-Codes, to select just the observations which had a diagnose contained in this list. Here a little example: %Macro test (dx=); proc sql; create table diag_heart as select patid, diag; from table1 where diag IN &dx; %Mend test; %test(dx=('I05%','I06%','I07%')); %test(dx=('G051%')); The problem is that the IN function doesnt know the %. Do you have any ideas? regards, marie

marieK · ‎08-20-2010

Hi Robert, thank you! i already found that imagename= option, but then both output graphs have these names (name.png and name1.png). 'LogNegLogSurvivalPlot.png' and 'SurvivalPlot.png' are automatically given names from SAS, i just want to add my variable name at the ending so that i dont loose the overview with 20 variables and still know if its the LogNegLogSurvival Plot or the Survival Plot 😉 So is the any chance to name them seperated?

marieK · ‎08-20-2010

Hey, i would like to rename the png-graphs from my proc lifetest (i have two different graphs: survival- and the Loglogs-graph). My output png-Graphs are named with 'LogNegLogSurvivalPlot.png' and 'SurvivalPlot.png'. i would like to add the variable name at the end so that i got 'LogNegLogSurvivalPlot_varname.png' and 'SurvivalPlot_varname.png'. Is that possible? thanks in advance. Marie

Online Status	Offline
Date Last Visited	‎05-12-2020 06:59 AM

Re: read CSV with infile and with comma as decimal point

read CSV with infile and with comma as decimal point

Re: Insert long text variable from SAS into Oracle database table

Re: Insert long text variable from SAS into Oracle database table

Insert long text variable from SAS into Oracle database table

Re: SAS function compress() in PROC SQL

SAS function compress() in PROC SQL

Re: handover a list in Proc SQL

handover a list in Proc SQL

handover a list in Proc SQL

Re: read CSV with infile and with comma as decimal point

Re: Insert long text variable from SAS into Oracle database table

Re: Insert long text variable from SAS into Oracle database table

handover a list in Proc SQL

Re: read CSV with infile and with comma as decimal point

read CSV with infile and with comma as decimal point

Re: Insert long text variable from SAS into Oracle database table

Re: Insert long text variable from SAS into Oracle database table

Insert long text variable from SAS into Oracle database table

Re: SAS function compress() in PROC SQL

SAS function compress() in PROC SQL

Re: handover a list in Proc SQL

handover a list in Proc SQL

handover a list in Proc SQL

handover a list in Proc SQL

handover a list in Proc SQL

handover a list in Proc SQL

Re: Name Graphics seperated

Name Graphics seperated