Help using Base SAS procedures

Assign ID

Reply
Contributor
Posts: 73

Assign ID

Hello

I am looking to create a dataset with ID or obs column.
New records will be appended each day to this dataset and i require them to automatically be given a id number.

Thanks

Fred
Super Contributor
Super Contributor
Posts: 3,174

Re: Assign ID

Suggest searching the forum archives - the question has been posed before (see suggested keywords below).

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search arguments, this topic / post:

assign unique id key variable new data site:sas.com

monotonic function site:sas.com Message was edited by: sbb
Contributor
Posts: 73

Re: Assign ID

Hi

I've been looking all morning on google and archive, can't seem to find what i'm looking for;

Fred
Contributor
Posts: 73

Re: Assign ID

Please look at my code and the resulting error

proc sql;
create table ccar.Tracking as
select *, monotonic() as rowid
from ccar.level_5_skillgroupid_null;
quit;

When i attempt to append data i get this error

variable id was not found in data file

Help please.

Fred
Valued Guide
Posts: 2,174

Re: Assign ID

please post proc contents report for the data sets
ccar.Tracking
and
ccar.level_5_skillgroupid_null
and
Dupes_virtual_queue if it exists
SAS Super FREQ
Posts: 8,740

Re: Assign ID

Hi:
Please note the warning in the Tech Support note:
http://support.sas.com/kb/15/138.html

cynthia

The note is quoted in its entirety:
[quote]
The MONOTONIC() function is not supported in PROC SQL. Using the
MONOTONIC() function in PROC SQL can cause missing or non-sequential
values to be returned.
[endquote]
SAS Super FREQ
Posts: 8,740

Re: Assign ID

Also, (aside from MONOTONIC issues) your SQL statement is creating a variable called ROWID, but your error message is pointing to the ID variable. What is the correct name of the variable???

cynthia
Contributor
Posts: 73

Re: Assign ID

Hi Cynthia

proc sql;
create table ccar.Tracking as
select *, monotonic() as rowid
from ccar.level_5_skillgroupid_null;
quit;

Above proc sql creates the table i am after with rowid.

My append query below returns error variable rowid was not found in data file.

proc append Base =ccar.tracking Data = Dupes_virtual_queue force;
run;


Please if you have a better solution let me know , been working with this all day.

fred
SAS Super FREQ
Posts: 8,740

Re: Assign ID

Hi:
Well, since you did not post the CONTENTS for your datasets (as requested by Peter C), it is hard to understand exactly what's going on. Your original version of the error message was:
variable id was not found in data file


But that was not an actual LOG message -- just what you reported was the log message. Now you say that the error is related to ROWID. I'd really like to see the ACTUAL SAS log -- for the ACTUAL code that you ran.

Also, Peter's request to see the CONTENTS was a good debugging technique. Clearly, you have a mismatch between the variables in these datasets:
ccar.Tracking
ccar.level_5_skillgroupid_null
Dupes_virtual_queue


If your original BASE= dataset has a variable called ROWID, how did ROWID get into that dataset???? You originally posted:
[pre]
proc append Base =tracking Data = Dupes_virtual_queue force;
run;
[/pre]

So, a comparison of the variables in WORK.TRACKING or CCAR.TRACKING is a very reasonable debugging technique.

How will you keep the ROWID or ID numbers unique everytime you do the append? Do the ROWID numbers need to be unique?? For example, consider the jprogram and output below -- instead of using MONOTONIC with PROC SQL, I just create an OBSNO column using the _N_ automatic variable in a DATA step program. There are obvious problems with this approach, which you will see when you review the output. But using _N_ -WITH- some other unique number (such as a date) or some other unique identifier or set of identifiers might be better than just a sequentially assigned observation number.

Just some thoughts and ideas.

cynthia

[pre]
data newclass;
length name $8;
set sashelp.class(obs=2);
obsno = _n_;
orig_file='work.newclass ';
name = catt('xx',name);
run;

proc append base=work.newclass data=sashelp.class(obs=2) force;
run;

proc print data=work.newclass;
title 'After First Append';
run;

data newclass2;
length name $8;
set sashelp.class(obs=5);
obsno = _n_;
orig_file='work.newclass2 ';
name = catt('yy',name);
run;

proc append base=work.newclass data=work.newclass2 force;
run;

proc print data=work.newclass;
title 'After Second Append -- Note OBSNO column now has duplicates';
run;
[/pre]

After the second append -- note how the OBSNO column now has duplicates (the OBS variable on the far left is from PROC PRINT) -- while the OBSNO column is the one I created in the. I also created a variable called ORIG_FILE -- so you could see which file contributed which observations to the appended dataset. The 2 observations with ORIG_FILE of blanks came into the file when the SASHELP.CLASS datset was appended with the WORK.NEWCLASS dataset. After doing the two appends, this is what the FINAL PROC PRINT output looks like:
[pre]

Obs name Sex Age Height Weight obsno orig_file

1 xxAlfred M 14 69.0 112.5 1 work.newclass
2 xxAlice F 13 56.5 84.0 2 work.newclass
3 Alfred M 14 69.0 112.5 .
4 Alice F 13 56.5 84.0 .
5 yyAlfred M 14 69.0 112.5 1 work.newclass2
6 yyAlice F 13 56.5 84.0 2 work.newclass2
7 yyBarbar F 13 65.3 98.0 3 work.newclass2
8 yyCarol F 14 62.8 102.5 4 work.newclass2
9 yyHenry M 14 63.5 102.5 5 work.newclass2
[/pre]
Contributor
Posts: 24

Re: Assign ID

define a data view over dataset you want to append and define the id varaible value there, and use proc append.

assume all ID will be unique, and the records number in base dataset (or the next ID value) is &nobs.

data temp/view=temp;
set new;
id=&nobs+_n_;
run;

proc append base=base data=temp;
run;

http://support.sas.com/forums/thread.jspa?threadID=11335
Contributor
Posts: 73

Re: Assign ID

Running this created error below

data temp/view=temp;
set ccar.dupes_virtual_queue;
id=&nobs+_n_;
run;

22 data temp/view=temp;
23 set ccar.dupes_virtual_queue;
24 id=&nobs+_n_;
-
22
WARNING: Apparent symbolic reference NOBS not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
a numeric constant, a datetime constant, a missing value, INPUT, PUT.

25 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds
SAS Super FREQ
Posts: 8,740

Re: Assign ID

Hi, Fred:
You have to CREATE &NOBS, there are several different ways, this is probably the one I would use:
[pre]
** before create ID variable, get number of obs in BASE dataset;
%let dsid = %sysfunc(open(XXX));
%let nobs = %sysfunc(attrn(&dsid, nobs));
%put number of obs in xxx is: &nobs;
[/pre]

so where I have XXX, you would put the name of your BASE= dataset (not in quotes). This will create the &NOBS macro variable. so if the name of your BASE dataset was PERM.MASTER, then the code would be:
[pre]
** before create ID variable, get number of obs in BASE dataset;
%let dsid = %sysfunc(open(PERM.MASTER));
%let nobs = %sysfunc(attrn(&dsid, nobs));
%put number of obs in PERM.MASTER is: &nobs;
[/pre]

Then you can USE &NOBS in your program where you are creating the UNIQUE ID for what will be appended to the BASE. This creation of the macro variable &NOBS has to occur BEFORE your usage of &NOBS. This means that the statements for the creation of &NOBS would have to be placed ABOVE the DATA step where you are creating the unique ID.

cynthia
Contributor
Posts: 73

Re: Assign ID

Hi Cynthia

Below is my code and then the resulting error, any suggestions?

%let dsid = %sysfunc(open(ccar.tracking));
%let nobs = %sysfunc(attrn(&dsid, nobs));
%put number of obs in ccar.tracking is: &nobs;

data ccar.Master/view=ccar.master;
set ccar.tracking;
id=&nobs+_n_;
run;


proc append Base =ccar.Master Data = ccar.Dupes_virtual_queue FORCE ;
run;


--------------------------------------------------------------------------------------------------------------------

proc append Base =ccar.Master Data = ccar.Dupes_virtual_queue FORCE ;
ERROR: Unable to open the read only view CCAR.MASTER for UPDATE.
46 run;

NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.25 seconds
cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.

Fred
SAS Super FREQ
Posts: 8,740

Re: Assign ID

First, I think the error message is quite clear -- you can't open the file CCAR.MASTER for write access because you set it up as a read only view.
ERROR: Unable to open the read only view CCAR.MASTER for UPDATE.


But the previous post showed using &NOBS with the data that you want to APPEND to the master -- not using &NOBS and making a view of the MASTER or BASE=. So I'm not sure you have the right concepts yet.

What is your BASE dataset??? CCAR.TRACKING or CCAR.MASTER. The dataset that you use in your %SYSFUNC is the BASE dataset -- The way I understand your problem is this

1) you already have a BASE= dataset with IDs (what is the name of this BASE dataset and how did the IDs get into this file -- I assume this file is CCAR.MASTER)
2) you need to create unique ids in a separate file so that you can append #2 file (CCAR.TRACKING) to #1 file (CCAR.MASTER)
3) So, in your %SYSFUNC, you need to figure out how many OBS are currently in the MASTER file #1 so you can correctly assign IDs in the new file #2
4) That means you will need to use the #1 file name (NOT the #2 file name) in the %SYSFUNC statement.
5) this also means that you will make a view from the #2 file -- and append the view to the BASE= file (once unique IDs have been assigned in the view)

If you do NOT already have unique ids in the #1 file (CCAR.MASTER) Then you will need to create them -- but you would NOT add &NOBS to the observations in the current MASTER file.

So, you need to describe your current situation (CCAR.MASTER) either does or does not have unique IDs before the first append. Also, please verify which is the correct BASE= CCAR.MASTER or CCAR.TRACKING.

cynthia
Contributor
Posts: 73

Re: Assign ID

Once again thanks for all your help and patience.

I have just started a new job in June 2010 and without any previous knowledge of SAS inherited several programs and have built a few, very big learning curve.

I will work harder to going forward to be clear and include examples for any future posts.

Cynthia your last reply clarified it for me and I got it to work.

Thanks

Fred
Ask a Question
Discussion stats
  • 17 replies
  • 1222 views
  • 0 likes
  • 5 in conversation