topic Re: Recognizing random numbers from 1 til 3 billion. in SAS Procedures

Recognizing random numbers from 1 til 3 billion.

Damvad — Tue, 22 Dec 2009 13:47:15 GMT

I have a dataset of approximately 5000 observations.

Each observation is a firm, and each firm is part of a program. These programs have, over time, been inappropriately named values between 0 and 3 billion. All firms (in program X), have a project-id value of Y, but Y differs ALOT over the dataset.

Some of the examples of the project-id variable are 55051, 5,42, 23341212 etcetera.

So basically, what I’m looking to do, is for SAS to recognize (for example) the number 5,42 – and give a new variable a value of 1,01. The next step would be to find all the observations of 5,43, thus giving the new variable a values of 1,02, and so forth.

However, there are quirks. All of these seeming random program-id values, all fit under 14 large main programs (Z). If possible, program Z would be fitted with the same initial values, for an example 1,xxx.

Soooo. A quick resume, would be that I need SAS to run through numbers, from 1 to 3000000000, and every time SAS finds a unique value, call it something like n,(n-10)(n-1) for non-negative numbers.

I’ve been struggling with this problem all day, and I’m absolutely out of ideas.
I would appreciate any help that would point me in the right direction.

Hope you can help.

Kind regards
Tobias

Re: Recognizing random numbers from 1 til 3 billion.

Peter_C — Tue, 22 Dec 2009 14:49:50 GMT

how about the simple thing first ;[pre]* just one series ;
proc sort data= your.original out= sorted1 ;
by project_id ;
run ;
data series_1 ;
set sorted_1 ;
by project_id ;
key1 + first.project_id ;
run ;[/pre]
That pays no respect to the "14 large main programs (Z)", because you offer no way of detecting this from your description.
If your "original" holds a variable Z which is the relevant "large main program" for each row, then my code could be revised like:[pre]* two series ;
proc sort data= your.original out= sorted2 ;
by Z project_id ;
run ;
data series_2 ;
set sorted_2 ;
by Z project_id ;
if first.Z then do ;
key1 + 1 ;
sub_key = 0 ;
end ;
sub_key +first.project_id ;
length new_key $9 ;
new_key = cats( key1, ',', sub_key ) ;
drop key1 sub_key ;
run ;[/pre]Of course, without a proper set of test data, and therefore no proper understanding of your data model, this will probably not achieve what you needed.
Would you like to provide some sample data (say 20 rows)?

Re: Recognizing random numbers from 1 til 3 billion.

Damvad — Tue, 22 Dec 2009 15:18:18 GMT

Hi Peter, thanks for the reply!

The problem with detecting the 14 large maingroups, i have a solution for myself. It's no problem to identify the 14 programs and have a sheet for each of them.

I can't figure out how to attach an excelfile?

I've taken this ss of the data in an sheet. The data marked with green is the project-id, and the data marked with red are the firms involved.

http://dl.dropbox.com/u/1321324/Pics/test.JPG Message was edited by: Damvad

Re: Recognizing random numbers from 1 til 3 billion.

Damvad — Tue, 22 Dec 2009 15:27:07 GMT

Key1 counted up to 1864 different programs. I actually think, that i might know how to get on from here.

I'll return with my solution, thanks alot for the help. That "key1 + first.projektid ;" is a usefull string of text 🙂

/T

Re: Recognizing random numbers from 1 til 3 billion.

sbb — Tue, 22 Dec 2009 15:37:06 GMT

OP reply: I can't figure out how to attach an excelfile?

Attach it to an EMAIL or as a web-page hyperlink?

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search arguments for each option mentioned:

attach excel file email site:sas.com

generate hyperlink site:sas.com