%macro InsertTable(pSourceTable=, pLoadMethod=, pDataTransferID=); proc sql; insert into <TableName> ( Column1, Column2, Column3, Column4 ) select Column1, Column2, case when &pLoadMethod ='D' then Column3 when &pLoadMethod ='F' then &pDataTransferID. end as Column3, case when &pLoadMethod ='D' then Column4 when &pLoadMethod ='F' then 'I' end as Column4 from &pSourceTable.; quit; %mend InsertTable; %InsertTable(pSourceTable=SourceTable, pLoadMethod='D', pDataTransferID=1); I think you are looking for the code that I have written above. I see problem in case statement. After Case keyword there should be variable name. I do not find any macro variable as LoadMethod in the given code. Suggest you to try the code with actual variable first. Once code run fine then replace the variables with macro variable.       case &LoadMethod. when 'D' then Column3 when 'F' then &pDataTransferID. end as Column3, ... View more

To connect MS Access database, we need to create ODBC connection.  Need to create ODBC string then use that string to create MS Access Server in SAS Management console  Then you can either create library using wizard or write LIBNAME statement to use MS Access table    For more details refer to following link http://support.sas.com/documentation/cdl/en/bidsag/68193/HTML/default/viewer.htm#n167cjb87i0x9wn1h1qynnb2zumc.htm https://support.sas.com/documentation/cdl/en/acpcref/63181/HTML/default/viewer.htm#n1wvmggexroxgyn17rp61jml3cvn.htm ... View more

Yes I can do that. But I have many such data step so I would end up writing too many code.  I will go by this solution, if there are none. ... View more

I really do not understand your problem fully. It would be nice if you can give more description. Otherwise try below code.   data X; input Y; datalines; 2 ; ... View more

  I need @RW9 input whether my understanding is correct that I have written down below.    PROC SQL; CREATE TABLE sql_where AS SELECT * FROM essentia.olympic WHERE year = 2012; QUIT; Above step would restrict rows in PDV. Only those row would come in PDV that has year=2012.            PROC SQL; CREATE TABLE sql_where (WHERE= (year=2012)) AS SELECT * FROM essentia.olympic; QUIT; The second approach would read every row in PDV irrespective of year =2012 and later on it will restrict while writing output dataset. Therefore this approach would definately take much longer time   ... View more

data have; infile datalines truncover; input ID VAR_MALAYSIA :$32. VAR_USA :$32.; datalines; 1 KUALA_LUMPUR CALIFORNIA 2 KELANTAN ALASKA ; run; proc transpose data=have out=transposed_have; by ID; var VAR_MALAYSIA VAR_USA; run; data want(DROP=_NAME_ RENAME=(COL1=STATE)); set transposed_have; if _NAME_ = 'VAR_MALAYSIA' then COUNTRY='MALAYSIA' ; if _NAME_ = 'VAR_USA' then COUNTRY='USA' ; RUN; You need to work more on the last data step if there are many more countries. ... View more

I am not sure on this, but you can explore that if we can apply an option that table lock should be row level.   Check the advance properties of Library. But this has to be approved by your DBA ... View more

This is an interesting question. Let me try if I am able to put my point   Through metadata function you can only see whatever gets updated in metadata. When a table is locked by SAS EG, there is no change in metadata as such. The lock is put on the physical table not the metadata. Thats why you can't view using Open metadata Interface or SAS metadata_getattr function.   Now question would arise what about the "LockedBy" attribute in metadata. When we check out any table using SAS DI then this attribute is updated. Value of Lockedby would be the URI of the user  who has checked out the object.  You can try this by making a check out of any table then find the Locked by value.    Using metadata function it is not possible to find locked table and to unlock it.      ... View more