BookmarkSubscribeRSS Feed
anirudhs
Obsidian | Level 7

Hi I want to code the fllowing code in EG using the query builder, can anyone help for doing the same in EG.

 

If Substr(Agency_Name,1,7)= 'INHOUSE' then

do;

Agency_Name ='INHOUSE';

Agency_Code ='INHOUSE';

end;

Else If Substr(Agency_Name,1,7)= '' then

 

do;

Agency_Name = 'UNALLOCATED';

Agency_Code = 'UNALLOCATED';

End;

 

Thanks

5 REPLIES 5
anirudhs
Obsidian | Level 7

Hi please help want to code the following condition in EG  using the query builder.

 

 

If Substr(Agency_Name,1,7)= 'INHOUSE' then

do;

Agency_Name ='INHOUSE';

Agency_Code ='INHOUSE';

end;

Else If Substr(Agency_Name,1,7)= '' then

 

do;

Agency_Name = 'UNALLOCATED';

Agency_Code = 'UNALLOCATED';

End;

 

 

Thank you

anirudhs
Obsidian | Level 7
Agency _code & Agency_Name are two different variables... so when Agency_name is Unallocated then other variable be should also allocate the same value.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes.  Please refer to SQL programming help, each variable is "selected" from a datasource so you need at the end:

 

select case when substr(agency_name,1,7)="INHOUSE" then "INHOUSE"
            else "UNALLOCATED" end as agency_name,
case when substr(agency_name,1,7)="INHOUSE" then "INHOUSE"
else "UNALLOCATED" end as agency_code
...

You cannot set the value of two output variables in one select <variable> line, you need to select data as variable, data as variable,...

 

gamotte
Rhodochrosite | Level 12

Hello,

 

It is not said in the question if substr(agency_name,1,7) can be somethng else than 'INHOUSE' or ' '.

Also, substrn should be used instead of substr if the length of agency_name can be shorter than 7.

 

select case when substrn(agency_name,1,7)="INHOUSE" then "INHOUSE"
            when substrn(agency_name,1,7)=' ' then "UNALLOCATED" 
       else agency_name end as agency_name,
RW9
Diamond | Level 26 RW9
Diamond | Level 26

I assume the "query builder" creates SQL, so:

case when substr(agency_name,1,7)="INHOUSE" then "INHOUSE"
         else "UNALLOCATED" end

For the first variable and then for the second (as each variable is separate in a select clause):

case when substr(agency_name,1,7)="INHOUSE" then "INHOUSE"
         else "UNALLOCATED" end

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
  • 5 replies
  • 1067 views
  • 0 likes
  • 3 in conversation