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 .
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;
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
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
Please post some example data that illustrates the full scope of the problem, including the field to link the rows.
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);
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
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.
I want employee_id for spouse as S+ employee_id of the spouse
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.