01-05-2016 03:14 PM
Are you using BASE SAS or EG?
If you simply want to rename a column I recommend using PROC DATASETS to rename the variable.
proc datasets library=work nolist nodetails; modify DATASET_NAME; rename employee_id=spouse_id; run;quit;
01-05-2016 03:18 PM
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.
01-05-2016 03:51 PM
01-05-2016 04:07 PM
01-05-2016 03:16 PM
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);
01-05-2016 05:06 PM
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
01-05-2016 03:20 PM
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.
01-05-2016 05:20 PM
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;