BookmarkSubscribeRSS Feed
aloou
Obsidian | Level 7

Hello,

 

I have a table that may contain 1 to 5 observations, i need to add a column that contains the number of observations.

i tried row_num= _n_ but it is not what i want.

i will be thankful for your help.

10 REPLIES 10
PeterClemmensen
Tourmaline | Level 20

You want the number of observations in every row, correct? 

 

If so

 

data want;
   set sashelp.class nobs = nobs;
   n = nobs;
run;
aloou
Obsidian | Level 7
No not the number of observations in every row.
like for example the table contains 5 observations , i want the new column to be like :
1
2
3
4
5
PeterClemmensen
Tourmaline | Level 20

Then your initial attempt is good

 

data want;
   set sashelp.class;
   n = _N_;
run;
aloou
Obsidian | Level 7
i tried it again in a table with two observations, it writes
1
1
PeterClemmensen
Tourmaline | Level 20

Show me your code?

 

The _N_ variable gives you the number of times the data step has iterated. Not the input observation number.

aloou
Obsidian | Level 7
data casuser.test;
set CASUSER.CHOIXC;
n = _N_;
run;
i need to put the observation number and not how many times it iterated.
Rick_SAS
SAS Super FREQ

From your code, it looks like you might be running this DATA step in CAS.  In CAS tables, there are no "observation numbers" because there is not an intrinsic order to the rows. They can be processed in any order.

 

In Viya 4, you can use the ADDROWID=YES option to add a special variable called _ROWID_ 

SAS Help Center: ADDROWID= Data Set Option

 

In earlier versions of CAS, you can combine the _N_ and the _THREADID_ variables to get a unique ID. See

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/casdspgm/p1myr5r0z9n55vn1c0ijy5wt6f8g.htm#...

and 

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/casdspgm/p10ux48pz7qknzn1ux1bs48vplnx.htm#...

 

 

Quentin
Super User

Update:  I think the below will not work on CAS running multi-threaded, i.e. I think it would result in duplicate values for N.  See e.g. https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/casdspgm/p10ux48pz7qknzn1ux1bs48vplnx.htm#... .  Perhaps if you added /single=yes to force it to run single-threaded it would work.  And perhaps adding /single=yes to the original code using _N_ would work.

 

___

 

I haven't tried CAS... if you just want a counter, maybe the sum statement would be an option?

data casuser.test;
  set CASUSER.CHOIXC;
  n + 1 ;
run;

Would N be unique in that case, or would the multi-threading result in there being duplicate values?  I understand that there is no intrinsic ordering, so if you ran the step multiple times, you might get different results. 

BASUG is hosting free webinars Next up: Mark Keintz presenting History Carried Forward, Future Carried Back: Mixing Time Series of Differing Frequencies on May 8. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Patrick
Opal | Level 21

@Quentin I can see in the docu for Viya that the RETAIN statement is supported https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsref/p0t2ac0tfzcgbjn112mu96hkgg9o.ht...

Really curious if that will cause CAS to execute in a single thread or if it means the data step will execute in SPRE. @Rick_SAS Do you know?

 

 

 

Quentin
Super User

Thanks @Patrick.  I just found this example in the docs:

 

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/casdspgm/p10ux48pz7qknzn1ux1bs48vplnx.htm#...

 

Which I think answers your question and mine:

  • When you use a retained variable to hold a sum in a multithreaded DATA step, the variable contains only the sum of the values that the processing thread can see.

In order to sum a variable across an entire dataset, they use retain in a first data step that runs multithreaded, and then run a second data step and force it to be single-threaded.

 

I haven't thought much about Viya, since I don't have it.  But I guess if you're used to thinking about the data step having a PDV, when it's multi-threaded it sounds like each thread has it's own PDV. 

 

So in the first step of the example:

data mycas.sums;                     
  retain homeowners_sum;                 /* 1 */
  keep homeowners_sum;                   /* 2 */
  set mycas.purchase end = done;         /* 3 */
  if demog_ho = 1 then                          
     homeowners_sum + 1;                 /* 4 */
  if done then output;                   /* 5 */
run;

If this were running on SAS 9 or on Viya single-threaded, it would output one record.  But on CAS it looks like it outputs one record per thread.  Because each thread will have its own end= flag in its PDV. I guess. : )

 

My understanding is when you submit code on Viya, Viya will decide whether a step will run on CAS or SPRE/Compute Server.  Kind of like implicit pass-through deciding whether to run a query in SAS or remote database.  But I generally don't like guessing where my code will run.  Do you know if there is a system option I can run in a Viya session that will say "please run all this code on SPRE/Compute Server"?  

 

I'm looking forward to upcoming Ask the Expert on converting SAS 9 code to Viya, hopefully it will address some of these differences.  https://communities.sas.com/t5/Upcoming-Events/How-Do-I-Modify-SAS-9-Programs-to-Run-in-SAS-Viya/ec-...

 

 

 

 

BASUG is hosting free webinars Next up: Mark Keintz presenting History Carried Forward, Future Carried Back: Mixing Time Series of Differing Frequencies on May 8. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 10 replies
  • 791 views
  • 5 likes
  • 5 in conversation