BookmarkSubscribeRSS Feed
14sas
Calcite | Level 5

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 .

21 REPLIES 21
Reeza
Super User

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;
14sas
Calcite | Level 5

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

Reeza
Super User
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.
14sas
Calcite | Level 5

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

 

Reeza
Super User
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.
14sas
Calcite | Level 5
There are specific ids ,which explain thier relation ship .
Reeza
Super User

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

14sas
Calcite | Level 5
emp _id relationshp_cd uniq_id
1234 employee a123401
3456 employee a0987601
nil dependent a0987602
ballardw
Super User

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

 

 

14sas
Calcite | Level 5

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

Astounding
PROC Star

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.

14sas
Calcite | Level 5

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

Reeza
Super User

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;
14sas
Calcite | Level 5
when i try this ,emp_id for dependents coming up only with prefix instead of prefix+emp_id .

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 21 replies
  • 3268 views
  • 1 like
  • 5 in conversation