Number of observation in dataset

[aloou]

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.

[PeterClemmensen]

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

If so

data want;
   set sashelp.class nobs = nobs;
   n = nobs;
run;

[aloou]

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]

Then your initial attempt is good

data want;
   set sashelp.class;
   n = _N_;
run;

[aloou]

i tried it again in a table with two observations, it writes
1
1

[PeterClemmensen]

Show me your code?

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

[aloou]

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]

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]

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. 

[Patrick]

@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]

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-...