DATA Step, Macro, Functions and more

how to add prefix to a column

Reply
Contributor
Posts: 20

how to add prefix to a column

i do have two columns where employee got his ID and i need to add employee id with prefix s to the spouse column .

i am confused about the synatx for spouse coumn .Thanks .

Super User
Posts: 19,770

Re: how to add prefix to a column

Are you using BASE SAS or EG?

 

If you simply want to rename a column I recommend using PROC DATASETS to rename the variable.

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a000247719.htm

 

proc datasets library=work nolist nodetails;
modify DATASET_NAME;
rename employee_id=spouse_id;
run;quit;
Contributor
Posts: 20

Re: how to add prefix to a column

Reeza i am using Base SAS .hERE IS THE CONDITION :

 

WHENEVER YOUR RELATIONSHIP CODE IS SPOUSE ,YOUR EMPLOYEE ID COLUMN HAS TO BE PREFIX+EMPLOYEEID.i AM REALLY CONFUSED BECAUSE ,SPOUSES GENERALLY WILL NOT HAVE EMPLOYEE ID HAS THIER ARE DEPENDENTS.

Thanks

Super User
Posts: 19,770

Re: how to add prefix to a column

Please post some sample data and expected output. If you don't understand the question or what you need to do, it's very hard for anyone else to suggest something without fully understanding your environment and data.
Contributor
Posts: 20

Re: how to add prefix to a column

here is the data :

 

emp_id   relationshp_cd 

1234       employee

 

nil              dependent

 

now the output has to be :

emp_id     relationshp_cd

1234       employee

S1234     DEPENDENT

 

Super User
Posts: 19,770

Re: how to add prefix to a column

I understand the request, but I think you'll need to post more sample data. How do I know that the dependent is linked to the employee for example? I'm assuming there's another field that shows the relationship or another table.
Contributor
Posts: 20

Re: how to add prefix to a column

There are specific ids ,which explain thier relation ship .
Super User
Posts: 19,770

Re: how to add prefix to a column

Please post some example data that illustrates the full scope of the problem, including the field to link the rows.

Contributor
Posts: 20

Re: how to add prefix to a column

emp _id relationshp_cd uniq_id
1234 employee a123401
3456 employee a0987601
nil dependent a0987602
Super User
Posts: 11,343

Re: how to add prefix to a column

Do you already have a spouse variable in a data set?

Are you updating an existing data set or creating a new one?

Is the employee id numeric or character?

Is the prefix numeric or character?

Is the spouse variable supposed to be numeric or character?

 

A generic for character variables could be

spouse = cats(prefix,id);

 

 

Contributor
Posts: 20

Re: how to add prefix to a column

The spouse column already exists as membership_cd as dependent , employee_id is numeric, in my desired output whenever membership_cd is dependent , in the employee_id it has to be Semployee_id , where s is the character adding to employee_id

Super User
Posts: 5,498

Re: how to add prefix to a column

Making a few assumptions what your answers will be concerning the incoming data:

 

if spouse > ' ' then spouse = 's' || employee_id;

 

This assumes that both of your incoming variables are character, and that SPOUSE is long enough to hold the "S" plus the ID.

 

I would assume that if there is no current value for SPOUSE, then you would like to keep it blank.

Contributor
Posts: 20

Re: how to add prefix to a column

Posted in reply to Astounding

I want employee_id for spouse as S+ employee_id of the spouse 

Super User
Posts: 19,770

Re: how to add prefix to a column

This basically a look up. Join the table to itself to find the corresponding record and update the field. 

 

I've modified your data slightly to make it easier to join for me, you'll have to tweak it to match your actual data conditions, but hopefully it gives you an idea of how to get started. If you need more help, please explain what doesn't work and provide more sample data that aligns with your data.

 

/*Generate sample data*/
data have;
infile cards dsd truncover;
informat relationshp_cd $12.;
input  order_var emp_id $  relationshp_cd $  uniq_id $;
cards;
1, 1234, employee,  a123401
2, 3456, employee,  a987601
3,    , dependent, a987602
;
run;

/*Create Spousal ID*/
proc sql;
create table want as
select a.order_var,
       coalesce(a.emp_id, cats("S", b.emp_id)) as emp_id,
		a.relationshp_cd,
		a.uniq_id
from have as a
left join have as b
/*this is the condition you need to modify to meet your joining criteria*/
on substr(a.uniq_id, 1, 5)=substr(b.uniq_id, 1, 5)
and b.relationshp_cd='employee'
order by a.order_var;
quit;

proc print data=want;
run;
Contributor
Posts: 20

Re: how to add prefix to a column

when i try this ,emp_id for dependents coming up only with prefix instead of prefix+emp_id .
Ask a Question
Discussion stats
  • 21 replies
  • 459 views
  • 1 like
  • 5 in conversation