topic Re: Create variable using catx and convert to numeric in SAS Procedures

Create variable using catx and convert to numeric

Cruise — Wed, 27 Dec 2017 19:00:39 GMT

I'd like to create a unique id combining two variables, year and id.

all I want is 20104961387856, 2015496138, 201149613878 in numeric format as a new variable. The demo below doesn't demonstrate the problem. It creates character variable "uid". However, using my actual large dataset (N=1,5M) uid is created as numeric variable and also found in sci-notation when original variable gets more than 8 digits as shown in the image. Consequent proc freq doesn't work with uid in sci-notification. I'm surprised. I thought sci-not is just for display view. Any suggestions please? Again, I need new variable "uid" as 20104961387856, 2015496138, 201149613878 in numeric format.

data have; 
input year id;
datalines;
2010 4961387856
2015 496138
2011 49613878
;

DATA have1; SET have;
uid=cats(year,id);
run;

UID created from actual data

Re: Create variable using catx and convert to numeric

PaigeMiller — Wed, 27 Dec 2017 19:10:26 GMT

If I understand you properly, the problem where the UID shows E12 is just formatting; what happens if you assign format 20.0 to the UID?

Re: Create variable using catx and convert to numeric

Cruise — Wed, 27 Dec 2017 19:18:31 GMT

@PaigeMiller

I just tried: uid=input(cats(year,id_bf),20.0);

and it had no effect on "uid". I still can see e12 and e13.

Re: Create variable using catx and convert to numeric

PaigeMiller — Wed, 27 Dec 2017 19:23:32 GMT

That's not really what I meant.

That affects the assignment of values to UID and not how the result is displayed.

How about

DATA have1; SET have;
uid=cats(year,id)+0;
format uid 20.0;
run;

Re: Create variable using catx and convert to numeric

Cruise — Wed, 27 Dec 2017 19:29:36 GMT

thanks for a solution. do you know why sci-notation affected my further programming? isn't it supposed to be for a display purpose only?

Re: Create variable using catx and convert to numeric

Quentin — Wed, 27 Dec 2017 20:28:46 GMT

Yes, formats are for display purposes. When you run PROC freq, it uses a format to display the result. So yes, formats effect the output of PROC freq.

Compare the output from these two FREQ steps:

data have;
  do id=20104961387856, 2015496138, 201149613878;
    output;
  end;
run;

proc freq data=have;
 tables id;
run;

proc freq data=have;
 tables id;
 format id 20.;
run;

When you have a long numeric value like this, you will want to make sure you won't exceed the limit of SAS's numeric precision on your OS. On Windows and unix, the maximum integer SAS can store precisely is 9,007,199,254,740,992. See http://documentation.sas.com/?docsetId=lrcon&docsetTarget=p0ji1unv6thm0dn1gp4t01a1u0g6.htm&docsetVersion=9.4&locale=en#p1ikmedbli0rz2n1de001ryz895o.

Often it makes to store such values in character variables, to avoid precision issues.

Re: Create variable using catx and convert to numeric

PaigeMiller — Wed, 27 Dec 2017 20:38:05 GMT

Why do you need these UID variables to be numeric, anyway? It seems like you are just adding work for yourself. It's not like you are going to try to find that average of UID, that doesn't make sense.

Re: Create variable using catx and convert to numeric

mkeintz — Thu, 28 Dec 2017 18:17:20 GMT

A minor quibble: 9,007,199,254,740,992 (call it X) is the maximum consecutive integer SAS can precisely store on windows and unix:

In the "rarely needed but worth knowing" category, SAS can also exactly store these integers, all larger than X:

All even integers X+2 through 2X (other integers are rounded up or down)
then all the 0mod4 (integers exactly divisible by 4) up through 4X
then all the 0mod8 integers up through 8X
etc.

Demonstration:

data _null_;

x=constant ('exactint');

put x=comma21.0;

do delta=0 to 8;

x2=x+delta;

put delta= x2=comma21.0;

end;

run;