topic Re: how to add prefix to a column in SAS Programming

how to add prefix to a column

14sas — Tue, 05 Jan 2016 19:18:30 GMT

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 .

Re: how to add prefix to a column

Reeza — Tue, 05 Jan 2016 20:14:16 GMT

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;

Re: how to add prefix to a column

ballardw — Tue, 05 Jan 2016 20:16:08 GMT

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);

Re: how to add prefix to a column

14sas — Tue, 05 Jan 2016 20:18:09 GMT

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

Re: how to add prefix to a column

Astounding — Tue, 05 Jan 2016 20:20:37 GMT

Making a few assumptions what your answers will be concerning the incoming data&colon;

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.

Re: how to add prefix to a column

Reeza — Tue, 05 Jan 2016 20:51:52 GMT

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.

Re: how to add prefix to a column

14sas — Tue, 05 Jan 2016 20:59:50 GMT

here is the data &colon;

emp_id relationshp_cd

1234 employee

nil dependent

now the output has to be :

emp_id relationshp_cd

1234 employee

S1234 DEPENDENT

Re: how to add prefix to a column

Reeza — Tue, 05 Jan 2016 21:07:41 GMT

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.

Re: how to add prefix to a column

14sas — Tue, 05 Jan 2016 21:13:19 GMT

There are specific ids ,which explain thier relation ship .

Re: how to add prefix to a column

Reeza — Tue, 05 Jan 2016 21:18:29 GMT

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

Re: how to add prefix to a column

14sas — Tue, 05 Jan 2016 21:38:43 GMT

emp _id relationshp_cd uniq_id
1234 employee a123401
3456 employee a0987601
nil dependent a0987602

Re: how to add prefix to a column

14sas — Tue, 05 Jan 2016 22:06:37 GMT

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

Re: how to add prefix to a column

14sas — Tue, 05 Jan 2016 22:08:53 GMT

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

Re: how to add prefix to a column

Reeza — Tue, 05 Jan 2016 22:20:28 GMT

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;

Re: how to add prefix to a column

Tanmay — Wed, 06 Jan 2016 11:45:34 GMT

You can also try this

New_Col = Compress(Prefix||Your_col);

Re: how to add prefix to a column

14sas — Wed, 06 Jan 2016 15:14:28 GMT

this wont be a new column ,just whenever your relationship_cd is dependent ,emp_id has to be prefix + emp_id .

Re: how to add prefix to a column

14sas — Wed, 06 Jan 2016 15:25:59 GMT

when i try this ,emp_id for dependents coming up only with prefix instead of prefix+emp_id .

Re: how to add prefix to a column

Reeza — Wed, 06 Jan 2016 15:28:46 GMT

Then either your join condition is incorrect or you need to trim the fields of blanks before concatenating them. Post your code.

Re: how to add prefix to a column

14sas — Wed, 06 Jan 2016 16:35:21 GMT

This is my program:

proc sql;

create table TEST as

where relationship_cd ='s' then employee_id = "S"II ''II (employee_id)

else trim (employee_id)

end as employee_id

from master_file

;

quit;

Re: how to add prefix to a column

Reeza — Wed, 06 Jan 2016 17:05:12 GMT

That's not valid code, there weren't errors in the log?