I am importing a file using proc import
proc import datafile="/opt/Export_d.csv" replace
out=work.export_data; run;
THEN I SELECTED 2 VARIBLES FROM EXPORTED TABLE HAVING SOME CONDITION
proc sql;
create table work.login2
as select ' SURVEY_ID'n, TIME format=datetime20.,' USERNAME'n
from work.export_data
having TIME=max(TIME) and ' USERNAME'="sasdemo";
quit;
then i created a macro for survey_id because i have to update a value with the Survey_id reference in different table shown in below code:
proc sql;
select max(' SURVEY_ID'n) into: sid from work.login2;
quit;
now if the ID is blank then i dont have to insert anything but if we have survey_id we have update few columns in a table using below code which is not happening.
proc sql;
case when ' SURVEY_ID'n is not null then update table_name set SAS_TRACKING_CODE="&Trackingcode."
where SURVEY_ID=&sid.
else end;
quit;
Then run two updates:
proc sql;
update table_name
set SAS_TRACKING_CODE = "&Trackingcode."
where SURVEY_ID=&sid. and SURVEY_ID is not null;
update table_name
set SAS_TRACKING_CODE = "&another_Trackingcode."
where SURVEY_ID is null;
quit;
Use a data step:
/* let's get rid of those stupid column names containing blanks in the first step */
proc import
datafile="/opt/Export_d.csv"
out=work.export_data (rename=(' SURVEY_ID'n=survey_id ' USERNAME'n=username))
replace
;
run;
proc sql;
create table work.login2 as
select
survey_id,
TIME format=datetime20.,
username
from work.export_data
where username="sasdemo"
having TIME=max(TIME)
;
quit;
proc sql;
select max(survey_id) into: sid from work.login2;
quit;
/* use a data step to update, much simpler code */
data table_name;
set table_name;
if survey_id ne . and survey_id = &sid
then SAS_TRACKING_CODE = "&Trackingcode.";
run;
Edit: changed the first SQL to have a where and having clause.
@india2016 wrote:
I can not create a table because i don't have access. I just have insert and update access of that table.
Then your update should look like that:
proc sql;
update table_name
set SAS_TRACKING_CODE = "&Trackingcode."
where SURVEY_ID=&sid. and SURVEY_ID is not null;
quit;
Then run two updates:
proc sql;
update table_name
set SAS_TRACKING_CODE = "&Trackingcode."
where SURVEY_ID=&sid. and SURVEY_ID is not null;
update table_name
set SAS_TRACKING_CODE = "&another_Trackingcode."
where SURVEY_ID is null;
quit;
@india2016 wrote:
KurtBremser: I have one more doubt what if I dont want to do anything if the Survey_ID is null. Simply execute the proc. Will it work if I remove the 2nd update statment.
Yes. The
and SURVEY_ID is not null
will prevent that.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.