Playing around trying to learn FCMP.  Below is an example I can't figure out.  The hardcoded code and the function code are the same but the hardcoded actually works and function code will simple go the 0 date value?  I think it has sth to do the the input to arguement 1 but  its declared as text like it should be??  Appreciate your time and have a good one.     proc fcmp outlib= sasuser.funcs.clinical_project; function make8601v1( in1 $ , in2 ) $; temp = cat( put( in1 , IS8601DA.), "T" , put( in2 , TOD8. ) ) ; return( temp); endsub; run; data f1; A='11/22/2018'; B='10:14:00't; correct = cat( put( A , IS8601DA.), "T" , put( B , TOD8. ) ) ; from_func = make8601v1(A,B); run; ... View more

Practing SAS sql and curious on how to directly append another column from a seperate data set into a different data set without having a unique key to join on.  Say I table A with customers  and address in one table and Table B  has  customers and account number . Customers is not unique (lots of Joe Smith's in the world) but I know they were added to the system  in exactly the same order  and records are in a one-to-one manner.  How do I aggregate the two into a master table?  Sure its simple so sorry for being a bother.  Thanks !!   what I tried ------------------------------------------  data d1 set sashelp.cars; run;   proc sql; /*adding column */ alter table d1 add make char;   /*trying to insert make values back into the table */ update d1 set make = (select make from sashelp.cars);   quit; ... View more

Hows it going?  noob sas sql question here . Im trying to learn foreign references and what Im trying to do is establish a primary foreign relationship and then delete from the primary and have it auto delete in the table with an associated foreign key .  Say I have stopped selling cars > 30000 because no one is buying them.  I want to delete all the cars in my records  that match this condition from all linked tables by simply deleting the entries in the table with the primary key.  I thought that using DELETE CASCADE would accomplish this but I must be missing sth.  It says this is not supported or maybe my syntax is wrong?  Any help would be appreciated.   %macro make_sql; proc sql ; create table k1 (id num , model char , msrp num ) ; insert into k1 select monotonic()*2 as id , model , msrp from sashelp.cars; quit;   proc sql; alter table k1 add constraint prime primary key(id); run; proc sql ; create table k2 (id num , model char, inventory num ); insert into k2 select monotonic()*2 ,model, invoice from sashelp.cars; quit; proc sql ; alter table k2 add constraint foreign2 foreign key(id) references k1 on delete cascade ; quit; %mend make_sql;   %macro del_sql; proc sql; alter table k2 drop foreign key foreign2; drop table k1; quit; proc sql; drop table k1; quit; %mend del_sql;   %make_sql;   * condition to remove the primary values  proc sql; delete from k1 where msrp > 30000 ; quit;   *K1 deletes the values k2 stays the same? ; proc contents data=k1 ; run; proc contents data=k2; run;   ********************************************************* Ive also replaced ON DELETE CASCADE with ON UPDATE CASCADE  then  used below.  its not working either so I must be missing sth very basic. 😞   proc sql; update k1 set id = 0 where msrp > 30000 ; quit;     ... View more