topic Re: Need to merge and rename character to numeric in SAS Programming

Need to merge and rename character to numeric

mariamon0 — Mon, 09 Mar 2020 13:07:29 GMT

I have three different variables

ID: 0000 (4 numbers)

PID: 0000 (4 numbers)

and Study ID BOT000 (three numbers and three characters).

I want to make a new variable called ID_NEW

I am trying to merge all three to one variable.

/*DO; IF PID = . THEN PID = 0; END;

DO; IF ID = . THEN ID = 0; END;

DO; IF Study_ID=. then Study_id=0; End;

DO; ID_NEW = ID+PID+STUDY_ID; END;

Re: Need to merge and rename character to numeric

andreas_lds — Mon, 09 Mar 2020 13:26:00 GMT

@mariamon0 wrote:

I have three different variables

ID: 0000 (4 numbers)

PID: 0000 (4 numbers)

and Study ID BOT000 (three numbers and three characters).

I want to make a new variable called ID_NEW

I am trying to merge all three to one variable.

/*DO; IF PID = . THEN PID = 0; END;

DO; IF ID = . THEN ID = 0; END;

DO; IF Study_ID=. then Study_id=0; End;

DO; ID_NEW = ID+PID+STUDY_ID; END;

Please show the types of all variables used. The operator + is only valid for numeric variables, if you want to concatenate strings, you should use one of the cat-functions. So Maybe you can solve the problem by

/* before data step */ 
options missing = "0";

/* in a data step */
id_new=cats(id, pid, study_id);

/* after data step */
options missing=".";

Re: Need to merge and rename character to numeric

mariamon0 — Mon, 09 Mar 2020 13:32:21 GMT

study_id is a character variable and ID and PID are numeric.

The Cats function is not working

data web.new;

set ESC.old;

id_new=cat(id,pid,study_id);

run;

Re: Need to merge and rename character to numeric

Tom — Mon, 09 Mar 2020 13:39:38 GMT

@mariamon0 wrote:

study_id is a character variable and ID and PID are numeric.

The Cats function is not working

data web.new;

set ESC.old;

id_new=cat(id,pid,study_id);

run;

Do not force SAS to guess how to define a new variable, you might now like the decision it makes. Define the type and storage length yourself. So if ID is length $10 and you want to convert the two numbers to four digits each then ID_NEW needs to be length $18.

Do not force SAS to guess how to convert values between types, you might not like the decision it makes.

length id_new $18.
id_new=cats(id,put(pid,Z4.),put(study_id,Z4.));

Re: Need to merge and rename character to numeric

mariamon0 — Mon, 09 Mar 2020 15:06:46 GMT

the cats function is not recognized in my dataset...cant seem to get it to work

Re: Need to merge and rename character to numeric

Tom — Mon, 09 Mar 2020 15:13:13 GMT

@mariamon0 wrote:

the cats function is not recognized in my dataset...cant seem to get it to work

Datasets do not have functions.

What version of SAS are you running? It should appear at the start of the SAS log. Or look at the value of the automatic macro variables SYSVER and SYSVLONG.

12867  %put SYSVER  =&SYSVER  ;
SYSVER  =9.4
12868  %put SYSVLONG=&SYSVLONG;
SYSVLONG=9.04.01M5P091317

Re: Need to merge and rename character to numeric

ballardw — Mon, 09 Mar 2020 20:12:37 GMT

@mariamon0 wrote:

study_id is a character variable and ID and PID are numeric.

The Cats function is not working

data web.new;

set ESC.old;

id_new=cat(id,pid,study_id);

run;

You say CATS is not working but show CAT. They are two different functions.

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Instead of a bunch of if/then you might consider the COALESCE, for numeric values, or COALESCEC for character values.

Coalesce(PID, 0) will return the value of PID if not missing and 0 if PID is missing. So you might be looking for something like:

data web.new;
   set ESC.old;
   length id_new $ 14;
   id_new=cats(coalesce(id,0),coalesce(pid,0),coalescec(study_id,'0') );
run;

Re: Need to merge and rename character to numeric

andreas_lds — Tue, 10 Mar 2020 06:13:11 GMT

@mariamon0 wrote:

study_id is a character variable and ID and PID are numeric.

The Cats function is not working

data web.new;

set ESC.old;

id_new=cat(id,pid,study_id);

run;

I don't see the options-statement i posted, if you don't change the way missing numerics are displayed, cat[s] will simply omit the variable while concatenating.