DATA Step, Macro, Functions and more

Finding Out Which Dataset The Obs Came From In Using The Set Statement

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

Finding Out Which Dataset The Obs Came From In Using The Set Statement

Hi All

I create a dataset by setting many datasets together.  Is it possible to find out which dataset contributed the observation?

eg

Data Health;

     set one

           two

           threee

           four;

run;

I need to mark where each obs came from

Thanks

Steve


Accepted Solutions
Solution
‎10-19-2011 02:02 PM
Valued Guide
Posts: 765

Re: Finding Out Which Dataset The Obs Came From In Using The Set Statement

Hi ... yes, this is certainly less obscure than the LOG2 stuff to give 1, 2, 3, 4, etc. and also one of those "duh" moments ...


data all;

set one (in=_1) two (in=_2) three(in=_3);

from = find(catt(of  _Smiley Happy,'1');

run;

View solution in original post


All Replies
Valued Guide
Posts: 765

Re: Finding Out Which Dataset The Obs Came From In Using The Set Statement

Hi ... if you have V9.2 or later, you can use the INDSNAME option on the SET statement ...

data one two three;

retain x 1;

run;

data all;

set one two three indsname=dsn;

dset = dsn;

run;

x       dset

1    WORK.ONE

1    WORK.TWO

1    WORK.THREE

Super Contributor
Posts: 1,636

Finding Out Which Dataset The Obs Came From In Using The Set Statement

How about:

data health;

    set one (in=a)

          two (in=b)

          three(in=c)

          four(in=d);

if a then from=1;

if b then from=2;

if c then from=3;

if d then from=4;

run;

Contributor
Posts: 51

Finding Out Which Dataset The Obs Came From In Using The Set Statement

Hi Guys

Thanks for the replies.  Really liking the INDSNAME but unforunately don;t have 9.2..yet..still on 9.1.3

Just working on the suggestion from Linlin

Looks like it's gonna be the long way round but good to see 9.2 has a solution

Thanks for sharing your views

Steve

Valued Guide
Posts: 765

Re: Finding Out Which Dataset The Obs Came From In Using The Set Statement

Hi ... with that approach, you could also consider (assuming there are no variables in the data sets that start with an underscore) ...

data one two three;

retain x 1;

run;

data all;

length from $3;

set one (in=_1) two (in=_2) three(in=_3);

from1 = catt(of _Smiley Happy;

from2 =

run;

from    x

100     1

010     1

001     1

If you want the 1, 2, 3, 4 etc. instead of 1's and 0's, you could try (though there might be some less obtuse way to do this) ...

data all;

set one (in=_1) two (in=_2) three(in=_3);

from = log2(input(reverse(catt(of _Smiley Happy),binary.))+1;

run;

x    from

1      1

1      2

1      3


Respected Advisor
Posts: 3,777

Re: Finding Out Which Dataset The Obs Came From In Using The Set Statement

I'm thinkin' is there a REGEX that would change ONE TWO THREE to ONE(in=in1) TWO(in=in2) THREE(in=in3) and there probably is but I don't know without research and the rest.

So I try the following.  I did not expect it to work and of course I get disqualified for using MONOTONIC.  And the numbers change each time you run it but that doesn't matter because they just need to be different names for each IN=option, not necessarily 1 2 3.

8    %put NOTE: DATA=&data;

NOTE: DATA=one two three

9    %let data2=%sysfunc(compbl(%superQ(data)))%str( );

10   %let data2=%sysfunc(transtrn(%superq(data2),%str( ),%nrstr((in=_in%sysfunc(monotonic())) )));

11   %put NOTE: DATA2=%superq(data2);

NOTE: DATA2=one(in=_in1) two(in=_in2) three(in=_in3)

12

13   data all;

14      set &data2;

15      length in $8;

16      in = cats(of _IN:);

17      name = scan("&data",index(in,'1'));

18      run;

Obs    i    in     name

  1     1    100    one

  2     2    100    one

  3     1    010    two

  4     2    010    two

  5     1    001    three

  6     2    001    three

Solution
‎10-19-2011 02:02 PM
Valued Guide
Posts: 765

Re: Finding Out Which Dataset The Obs Came From In Using The Set Statement

Hi ... yes, this is certainly less obscure than the LOG2 stuff to give 1, 2, 3, 4, etc. and also one of those "duh" moments ...


data all;

set one (in=_1) two (in=_2) three(in=_3);

from = find(catt(of  _Smiley Happy,'1');

run;

Contributor
Posts: 51

Finding Out Which Dataset The Obs Came From In Using The Set Statement

Hi

I used the

find(catt(of _Smiley Happy, '1') statement and works perfectly.

Really interesting to break it down and see how the in= works and storage of the _vars in the PDV ie sequentially.

Apologies for not looking into all the other given replies.

Thanks all

Steve

PROC Star
Posts: 7,363

Re: Finding Out Which Dataset The Obs Came From In Using The Set Statement

If the input files can be a numbered list, then the following could be used:

data want;

  set test1-test3 indsname=fname;

  from=input(compress(fname,,'dk'),3.);

run;

Super User
Posts: 9,681

Finding Out Which Dataset The Obs Came From In Using The Set Statement

If you want table name:

data one two three; 
set sashelp.class; output  one two three;
run;
option mprint mlogic symbolgen;

%let t_list=work.one work.two work.three;




%macro tlist;
%let i=1;
%let name&i=%scan(&t_list,&i,%str( ));
%do %while(&&name&i ne );
 %let t_name&i=&&name&i(in=in&i) ;
 %let i=%eval(&i+1);
 %let name&i=%scan(&t_list,&i,%str( ));
%end;
data all ;
 set %do j=1 %to &i-1;
       &&t_name&j
     %end;
     ;
length indsname $ 40;
select;
 %do k=1 %to &i-1;
 when(in&k) indsname="&&name&k";
 %end;
 otherwise;
end;
run;
%mend list;
%tlist


 


Ksharp

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 570 views
  • 6 likes
  • 6 in conversation