BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
india2016
Pyrite | Level 9

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

10 REPLIES 10
Kurt_Bremser
Super User

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
Pyrite | Level 9
I can not create a table because i don't have access. I just have insert and update access of that table.
Kurt_Bremser
Super User

@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;
india2016
Pyrite | Level 9
KurtBremser: Thanks for your prompt reply but i have done this. I need to add one more condition where Survey_ID is null then I have to insert something else.
Kurt_Bremser
Super User

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
Pyrite | Level 9
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.
Kurt_Bremser
Super User

@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.

india2016
Pyrite | Level 9
KurtBremser: Thanks Kurt it worked.
india2016
Pyrite | Level 9
@Kurt_Bremser : And i have to write proc only for this.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 2998 views
  • 0 likes
  • 2 in conversation