topic Re: How to add row number in an empty dataset in SAS Enterprise Guide

How to add row number in an empty dataset

alepage — Thu, 15 Dec 2022 15:22:47 GMT

Hello,

I have an empty dataset which contains let's say var1 until var 10 plus the var n.

I would like to set n from 1 to 100. Thereafter, I will use this template to make a join with another table using n.

ex:

var1 var2 ... var10 n

...

100

How do we do that task ?

Re: How to add row number in an empty dataset

PaigeMiller — Thu, 15 Dec 2022 15:26:43 GMT

data want;
     length var1-var10 8;
     do n=1 to 100;
          output;
    end;
run;

Re: How to add row number in an empty dataset

alepage — Thu, 15 Dec 2022 15:34:11 GMT

In the dataset want, var1 until var10 and n already exist but there is no value in those.
I have tried:
data want;
set want
do n=1 to 100;
output;
end;
and it does not work ? Does the fact to add the length statement solve that issue ?

Re: How to add row number in an empty dataset

Tom — Thu, 15 Dec 2022 15:39:05 GMT

@alepage wrote:
In the dataset want, var1 until var10 and n already exist but there is no value in those.
I have tried:
data want;
set want
do n=1 to 100;
output;
end;
and it does not work ? Does the fact to add the length statement solve that issue ?

That data step will stop at the SET statement if the input dataset is empty.

So don't actually execute the SET statement.

data want;
  if 0 then set have;
  do n=1 to 100;
    output;
  end;
run;

Re: How to add row number in an empty dataset

Tom — Thu, 15 Dec 2022 15:40:16 GMT

I don't really understand why it matters if the dataset has 0 or 100 observations if there is no data there.

Re: How to add row number in an empty dataset

PaigeMiller — Thu, 15 Dec 2022 15:40:57 GMT

So describe this empty data set to me. How many rows? A zero row empty data set is different than a 100 row data set where there are no values in any of the columns.

IMPORTANT CONCEPT: Never say "it does not work" and then provide no other information. We don't know what you did and we don't know what happened. If something doesn't work, give us more information about what you did (the exact code) and what was wrong (errors in the log, then show us the log; or wrong output, then show us the wrong output and explain what you want).

Re: How to add row number in an empty dataset

ballardw — Thu, 15 Dec 2022 15:44:05 GMT

How many records do you currently have in your data set? 1? 0?

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

I would not expect the code you post to work as 1) missing ; on the SET statement so would generate an error when encountering the DO and 2) until a RUN or following data/proc statement the code doesn't execute at all.

data want;
set want
do n=1 to 100;
output;
end;

Re: How to add row number in an empty dataset

alepage — Thu, 15 Dec 2022 15:52:38 GMT

it is a good question.

imagine that I have generated a dataset A with var1-var4 values including n, also with values in it.

The I take the dataset B which contains var1 until var10 including n.

In that dataset B, i want to drop (var1 to var4) and keep n as below;

data b(drop= var1 var2 var3 var4);

set b;

stop;

run;

Then create new table as below

proc sql;
create table pilote_&startyear2._&endyear2._jointure_v001 as
select a.*
,b.*
from work.jointure as a
left join pilote_&startyear._&endyear._jointure_tmpl as b on(a.n1=b.n);
Quit;

where dataset a is like jointure and dataset b is like pilote_&startyear._&endyear._jointure_tmpl

Re: How to add row number in an empty dataset

Tom — Thu, 15 Dec 2022 15:58:03 GMT

Huh?

Why not just merge on the key variable?

data want;
  merge a(in=in1) b;
  by n;
  if in1;
run;

Then it does not matter if B has zero observations.

I think you seem to have gotten stuck on a SQL mode and so avoided the obvious solution.

Re: How to add row number in an empty dataset

Astounding — Thu, 15 Dec 2022 15:58:27 GMT

Missing semicolon on the SET statement would have an impact.

Re: How to add row number in an empty dataset

Astounding — Thu, 15 Dec 2022 16:00:45 GMT

Must add a STOP statement after the END statement, at a minimum to avoid the note about DATA step ending due to looping.

Re: How to add row number in an empty dataset

Tom — Thu, 15 Dec 2022 16:05:46 GMT

@Astounding wrote:

Must add a STOP statement after the END statement, at a minimum to avoid the note about DATA step ending due to looping.

Or if you want the N variable to be the first one you can just run the SET statement last.

data want;
  do n=1 to 3; output; end;
  set sashelp.class(obs=0);
run;