DATA Step, Macro, Functions and more

Code in EG using query builder

Reply
Contributor
Posts: 70

Code in EG using query builder

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

Super User
Super User
Posts: 9,840

Re: Code in EG using query builder

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
Contributor
Posts: 70

EG Coding case

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

Contributor
Posts: 70

Re: EG Coding case

Agency _code & Agency_Name are two different variables... so when Agency_name is Unallocated then other variable be should also allocate the same value.
Super User
Super User
Posts: 9,840

Re: EG Coding case

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

 

Super Contributor
Posts: 359

Re: EG Coding case

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,
Ask a Question
Discussion stats
  • 5 replies
  • 144 views
  • 0 likes
  • 3 in conversation