Hi everyone I am using the following code to connect ot oracle: proc sql; connect using datax as oracle; create table combined_user as select * from connection to oracle ( select distinct * from datax.dbo.process a left join datax.dbo.users b on a.id=b.id and a.location=b.location ); quit; This is working fine without issues.   Due to problems with id coding, I needed to remove some punctuations in the column id from the first table. i tried the following    proc sql; connect using datax as oracle; create table combined_user as select * from connection to oracle ( select distinct * from datax.dbo.process a left join datax.dbo.users b on compress(a.id,,'p')=b.id and a.location=b.location ); quit; which generated an error, possibly because oracle does not understand the command compress.  Any suggestions on how to solve this? I tried reg_replace but with no luck Another question, is there anything in oracle that can do something similar to matching by "like"?   ( select distinct * from datax.dbo.process a left join datax.dbo.users b on compress(a.id,,'p') like b.id and a.location=b.location ); quit; Kind regards Am ... View more

Hi everyone,  a quick question. i have a table (Table A) that contains two columns looking like this Group ID A 24 A 54 A 92 A 6 A 7 B 25 B 11 B 97 C 13 C 18 D 81 D 10   Then I have a very large table (table B) from which i want to extract data based on the group and ID in a proc sql. For a number of reasons, I wont be able to use a left join or inner join statement, so I am trying to come up with a different approach that would look like this      proc sql, create table TABLEC as select * from tableB where &selection the macro variable &selection should be generated dynamically based on tableA and should at the end look like this       (group=A and ID in (24, , 54, 92, 6, 7) ) OR (group=B and ID in (25, 11, 97)) OR (group=C and ID in (13, 18)) OR (group=D and ID in (81, 10)) The number and name of groups is variable so the code should account for that too. The only thing that wont change is the name of columns (group, ID). Has anyone done something similar before?   Yes, I know left join makes more sense but as I said, I need to this differently... Thanks Am   ... View more

Could anyone please direct me or give me reference or working example on how to perform intrim futility analysis for the difference in means between two groups? Kind regards AM ... View more

Hi everyone Having a mental block issue  I have a dataset that looks like this:   data Tabkle; input nr subject date anydtdte23.; format date date9.; cards; 1 1 01Jan2013 2 1 31Jan2013 3 1 02Mar2013 4 1 01Apr2013 5 1 01May2013 6 2 31May2013 7 2 30Jun2013 8 2 30Jul2013 9 2 29Aug2013 10 2 28Sep2013 11 2 28Oct2013 12 3 27Nov2013 13 3 27Dec2013 14 3 26Jan2014 15 3 25Feb2014 16 3 27Mar2014 17 4 26Apr2014 18 4 26May2014 19 4 25Jun2014 20 4 25Jul2014 21 4 24Aug2014 ; run; To make my question easier to understand i made the difference between the dates in the rows = 30 days What I want to do is to select observations (starting with the first from each group) where the date difference is > 40 days from the previous one.   So the code will fist take the first observation The second observation will be deleted because the date (obs2)-date(obs1) < 40 Then the third observation will be selected because date (obs3) - date (obs1) > 40 : here obs2 is deleted by previous step so comparison occurs with obs1 then obs 4 will be deleted because date(obs4)-date(obs3) <40 then obs 5 selected because ate (obs5) - date (obs3) > 40 : here obs4 is deleted by previous step so comparison occurs with obs3 This is for group 1 then it repeats for each group and so forth I have tried the following, it didnt work because it still compares with the observation that should have been seleted:   data table2; set table; by subject; if lag(date) ne . and date - lag(date) <40 then delete; run;   Neither did the following work proc sql; create table table2 as select distinct L.*, R.date as next_date format=ddmmyy10. from table as L inner join table as R on L.nr=R.nr and intck('day', L.date, R.date) > 40 order by L.nr, L.date ; quit; Any help in solving this is appreciated ... View more

Hi everyone, Could anyone please help me with a code that will allow the user to brow to the location of an excel file to import to SAS? I am using SAS 9.4 Base (not EG) Thank you  Kind regards AM ... View more

Hi everyone A quick one. i have a code that transforms a table (ICD10 into __ICD10):   proc transpose data=ICD10 out=_ICD10; var l:; run; proc transpose data=_ICD10 out=__ICD10; by _name_; var col:; run; data ___ICD10; set __ICD10; where col1 ne ''; run; The problem is that I need to do the same thing for ICD7, ICD8, ICD9 and ICD10. My question is, is there a way to care a macro version from the above code to call and use with  ICD7-ICD10 instead of repeating the code? All intermediate files can also be deleted to avoid cluttering.  Thanks ... View more