BookmarkSubscribeRSS Feed
Damvad
Calcite | Level 5
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
4 REPLIES 4
Peter_C
Rhodochrosite | Level 12
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)?
Damvad
Calcite | Level 5
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
Damvad
Calcite | Level 5
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 618 views
  • 0 likes
  • 3 in conversation