topic Re: Request for immediate assistance in SAS Procedures

Request for immediate assistance

zana — Fri, 18 Apr 2014 15:25:46 GMT

Hello everybody,

My table currently contains:

subject1 | subject2

2 | 4

2 | 5

2 | 6

2 | 8

4 | 7

4 | 11

6 | 9

6 | 10

6 | 12

10 | 15

10 | 16

13 | 14

16 | 17

I need change this to:

2 | 4 | 5 | 6 | 8 | 7 | 11 | 9 | 10 | 12 | 15 | 16 | 17

13 | 14

What is the best way to do this? Any suggestions would be helpful.

Re: Request for immediate assistance

Peter_C — Fri, 18 Apr 2014 19:54:02 GMT

proc transpose

by subject1

Re: Request for immediate assistance

zana — Fri, 18 Apr 2014 20:26:27 GMT

Dear Peter, thanks for your cooperation

You know that with "proc transpose by subject 1" output file will be:

I want a program that support the following:

According to subject1 researching in col 1 to col nth respectively, if find it in each these columns, query it, otherwise don’t any change it (e.g. 2 in subject column that it is not observed in each other columns).

Input data:

Expected output:

Re: Request for immediate assistance

Reeza — Fri, 18 Apr 2014 20:47:21 GMT

Look into Proc BOM if you have the appropriate license of course...

There's a good solution on here to this problem by either Ksharp or FriedEgg, try searching under the term recursive lookup perhaps.

Re: Request for immediate assistance

art297 — Fri, 18 Apr 2014 20:57:48 GMT

Are you just trying to obtain a unique subject list? If so, how about something like:?

data want (keep=subject);

set have;

subject=subject1;

output;

subject=subject2;

output;

run;

proc sort data=want nodupkey;

by subject;

run;

Re: Request for immediate assistance

Reeza — Fri, 18 Apr 2014 21:15:43 GMT

And a related thread:

Re: Request for immediate assistance

Ksharp — Sat, 19 Apr 2014 06:59:56 GMT

Reeza, thank you to refer to me and Matt . I have once done it for a sas user named sas_forum .

Here is what I got.

One more thing, if the following happened ,what are you going to do ?

subject1|col1|col2|col3|col4

2|4|5|6|8

99|7|6|.|.

Wait a minute..... I am thinking about a faster way. someone any thought ?

data have;
infile cards delimiter='|';
input subject1 $  subject2 $ ;
cards;
        2    |    4  
        2    |    5  
        2    |    6  
        2    |    8
        4    |    7  
        4    |    11  
        6    |    9  
        6    |    10
        6    |    12
        10  |    15  
        10  |    16  
        13  |    14  
        16  |    17
;
run;
proc sort data=have ;by subject1;run;
proc transpose data=have out=test(drop=_name_) ;
by subject1;
var subject2;
run;
proc sql noprint;
select quote(strip(name)) into : list1 separated by ','  from dictionary.columns where libname='WORK' and memname='TEST';
select name into : list2 separated by ' ' from dictionary.columns where libname='WORK' and memname='TEST'; 
select count(name) into : n from dictionary.columns where libname='WORK' and memname='TEST'; 
quit;
 %put &list1;
 %put &list2;

options compress=yes;
data want(keep=&list2 household);
/*to make speed faster*/
declare hash ha(hashexp : 20,ordered : 'a');
declare hiter hi('ha');
ha.definekey('count');
ha.definedata('count',&list1 );
ha.definedone();
declare hash _ha(hashexp: 20,ordered : 'a');
_ha.definekey('key');
_ha.definedone();
do until(last);
set test end=last;
/*Remove obs which variable's are all missing firstly*/
if cmiss(of &list2) lt &n then do;
count+1;
ha.add();
end;
end;
length key $ 40;
array h{*} $ 40 &list2 ;
/*copy the first obs from Hash Table HA into PDV*/
_rc=hi.first();
do while(_rc eq 0); *until the end of Hash Table HA;
/*assign a unique cluster flag(i.e. household)*/
household+1;
do i=1 to &n;
/*push not missing value of current obs into another Hash Table _HA*/
if not missing(h{i}) then do; key=h{i}; _ha.replace();end;
end;
/*start to run over Hash Table HA ,until can not find any more
observation which is the same cluster with current observation*/
do until(x=1);
x=1;
/*copy the first obs from Hash Table HA into PDV*/
rc=hi.first();
do while(rc=0);
found=0;
do j=1 to &n;
/*find whether any one of value is included in the current obs*/
key=h{j};rcc=_ha.check();
if rcc =0 then found=1;
end;
if found then do;
/*if any one of value is included,then push the obs which is copied from
Hash Table HA into Hash Tables _HA,flag it the same cluster with the
current obs and output it into dataset*/
do k=1 to &n;
if not missing(h{k}) then do;
key=h{k};_ha.replace();end;
end;
output;x=0; _count=count;*keep this found obs's
index;
end;
rc=hi.next();
/*remove the found obs from Hash Table HA,since it has been seared*/
if found then rx=ha.remove(key : _count);
end;
end;
/*clear up all the index which is the same cluster with the current obs*/
_ha.clear();
/*copy the first obs from Hash Table HA into PDV*/
_rc=hi.first();
end;
run;
data final;
 set want;
 by household;
 length m $ 400;
 retain m;
 array x{*} $ &list2;
 do i=1 to &n ;
  if not findw(m,strip(x{i})) and not missing(x{i}) then m=catx(' ',m,x{i});
 end;
 if last.household then do;output;call missing(m);end;
 keep household m;
run;

Xia Keshan

Message was edited by: xia keshan

Re: Request for immediate assistance

art297 — Sat, 19 Apr 2014 17:36:26 GMT

: I definitely misunderstood what the OP was asking and like your solution. My only recommendations concern (1) why you would bother to sort and transpose the data as both steps seem unnecessary and (2) while extremely minor, why take three passes in the proc sql call when only one is needed?

I think that the following has the same result as your original code:

data have;

infile cards delimiter='|';

input subject1 subject2;

cards;

2 | 4

2 | 5

2 | 6

2 | 8

4 | 7

4 | 11

6 | 9

6 | 10

6 | 12

10 | 15

10 | 16

13 | 14

16 | 17

;

proc sql noprint;

select quote(strip(name)), name, count(name)

into :list1 separated by ',',

:list2 separated by ' '

from dictionary.columns

where libname='WORK' and

memname='HAVE'

;

quit;

data need(keep=&list2 household);

declare hash ha(hashexp : 20,ordered : 'a');

declare hiter hi('ha');

ha.definekey('count');

ha.definedata('count',&list1 );

ha.definedone();

declare hash _ha(hashexp: 20,ordered : 'a');

_ha.definekey('key');

_ha.definedone();

do until(last);

set have end=last;

/*Remove obs which variable's are all missing firstly*/

if cmiss(of &list2) lt &n then do;

count+1;

ha.add();

end;

length key $ 40;

array h{*} $ 40 &list2 ;

/*copy the first obs from Hash Table HA into PDV*/

_rc=hi.first();

do while(_rc eq 0); *until the end of Hash Table HA;

/*assign a unique cluster flag(i.e. household)*/

household+1;

do i=1 to &n;

/*push not missing value of current obs into another Hash Table _HA*/

if not missing(h{i}) then do; key=h{i}; _ha.replace();end;

end;

/*start to run over Hash Table HA ,until can not find any more

observation which is the same cluster with current observation*/

do until(x=1);

x=1;

/*copy the first obs from Hash Table HA into PDV*/

rc=hi.first();

do while(rc=0);

found=0;

do j=1 to &n;

/*find whether any one of value is included in the current obs*/

key=h{j};

rcc=_ha.check();

if rcc =0 then found=1;

end;

if found then do;

/*if any one of value is included,then push the obs which is copied from

Hash Table HA into Hash Tables _HA,flag it the same cluster with the

current obs and output it into dataset*/

do k=1 to &n;

if not missing(h{k}) then do;

key=h{k};

_ha.replace();

end;

output;

x=0;

_count=count;*keep this found obs's index;

end;

rc=hi.next();

/*remove the found obs from Hash Table HA,since it has been seared*/

if found then rx=ha.remove(key : _count);

end;

/*clear up all the index which is the same cluster with the current obs*/

_ha.clear();

/*copy the first obs from Hash Table HA into PDV*/

_rc=hi.first();

end;

run;

data want;

set need;

by household;

length m $ 400;

retain m;

array x{*} $ &list2;

do i=1 to &n ;

if not find(m,strip(x{i})) and not missing(x{i}) then m=catx(' ',m,x{i});

end;

if last.household then do;

output;

call missing(m);

end;

keep household m;

run;

Re: Request for immediate assistance

zana — Sat, 19 Apr 2014 17:57:30 GMT

Hi, Arthur.

Thanks a lot for your cooperation.

Output of this code is not solution. Please see the question on here:

I need exactly the same cod. I run all the proposed code for those, but unfortunately none of them were implemented.

Re: Request for immediate assistance

zana — Sat, 19 Apr 2014 18:34:07 GMT

Hi, Ksharp.

thank you for your reply.

Your code did not works with the sample data.

[ERROR 68-185: The function CMISS is unknown, or cannot be accessed.

ERROR 68-185: The function FINDW is unknown, or cannot be accessed]

However, maybe my information was not enough. Please find exact sample data (but better) with more explanation at follow

I should inform you that i run all the proposed code for those (even yours), but unfortunately none of them were implemented (for those sample data).

zana

Re: Request for immediate assistance

art297 — Sat, 19 Apr 2014 19:33:48 GMT

What version of SAS are you using?

Re: Request for immediate assistance

zana — Sat, 19 Apr 2014 20:25:41 GMT

Dear Arthur Information of my SAS is: SAS 9.1.3 Service Pack 4. Windows Version 6.1.7601 I see some error when i run your code, as [ERROR 68-185: The function CMISS is unknown, or cannot be accessed]. So thanks zana

Re: Request for immediate assistance

Tom — Sat, 19 Apr 2014 20:41:15 GMT

Basically you need to build out connected sub-graphs (or sub-networks) of your data. You could use the SubGraphs macro posted above. Here is another macro that uses SQL queries to assign nodes to subnets.

%macro subnet

(in= /* Input table with pairs */

,out= /* Output table of input with &SUBNET variable added */

,nodes=nodes /* Output table of nodes with with subnet assigned */

,from=from /* First variable in the pair */

,to=to /* Second variable in the pair */

,subnet=subnet /* Name to use for SUBNET variable */

);

/*----------------------------------------------------------------------

SUBNET - Build connected subnets from pairs of nodes.

Start each new subnet by taking a node that is not currently assigned to

a subnet. Grow the subnet by adding nodes that are connected to the

current subnet and are not currently assigned a subnet.

----------------------------------------------------------------------*/

%local nodeid next getnext ;

%let nodeid=0;

%*----------------------------------------------------------------------

Put code to get unassigned node into a macro variable so it can be used

in two places in the program.

-----------------------------------------------------------------------;

%let getnext= select node into :next from &nodes where subnet=.;

proc sql noprint ;

*----------------------------------------------------------------------;

* Get list of all nodes ;

*----------------------------------------------------------------------;

create table &nodes as

select distinct . as subnet,&from as node from &in

union

select distinct . as subnet,&to as node from &in

;

*----------------------------------------------------------------------;

* Get next unassigned node ;

*----------------------------------------------------------------------;

&getnext;

%do %while (&sqlobs) ;

*----------------------------------------------------------------------;

* Set subnet to next node id ;

*----------------------------------------------------------------------;

%let nodeid=%eval(&nodeid+1);

update &nodes set subnet=&nodeid where node=&next;

%do %while (&sqlobs) ;

*----------------------------------------------------------------------;

* Get list of connect nodes for this subnet ;

*----------------------------------------------------------------------;

create table new as

select distinct a.&to as node

from &in a, &nodes b, &nodes c

where a.&from = b.node

and a.&to = c.node

and b.subnet = &nodeid

and c.subnet = .

;

*----------------------------------------------------------------------;

* Set subnet to next node id ;

*----------------------------------------------------------------------;

update &nodes set subnet=&nodeid

where node in ( select node from new )

;

%end;

*----------------------------------------------------------------------;

* Get next unassigned node ;

*----------------------------------------------------------------------;

&getnext;

%end;

*----------------------------------------------------------------------;

* Create output dataset by adding subnet number. ;

*----------------------------------------------------------------------;

create table &out as

select distinct a.*,b.subnet as &subnet

from &in a , &nodes b

where a.&from = b.node

;

quit;

%mend subnet ;

Here is program to call this for your data.

data have ;

input from to @@;

cards;

2 4 2 5 2 6 2 8 4 7 4 11 6 9 6 10 6 12 10 15 10 16 13 14 16 17

run;

options mprint;

%subnet(in=have,out=want);

proc sort data=nodes;

by subnet node ;

run;

data _null_;

set nodes ;

by subnet ;

if first.subnet then put subnet ': ' @ ;

put node @ ;

if last.subnet then put ;

run;

And the resulting display:

1 : 2 4 5 6 7 8 9 10 11 12 15 16 17

2 : 13 14

Re: Request for immediate assistance

art297 — Sat, 19 Apr 2014 22:07:12 GMT

I don't have a way of testing whether all of the hash methods work in 9.1 but, since your data are numbers, you can replace the two functions in 's code that are only available since 9.2:

Both the following, and Tom's suggested code, produce the same result, but Ksharp's suggested use of the hash method runs quite a bit faster:

data have;

infile cards delimiter='|';

input subject1 subject2;

cards;

2 | 4

2 | 5

2 | 6

2 | 8

4 | 7

4 | 11

6 | 9

6 | 10

6 | 12

10 | 15

10 | 16

13 | 14

16 | 17

;

proc sql noprint;

select quote(strip(name)), name, count(name)

into :list1 separated by ',',

:list2 separated by ' '

from dictionary.columns

where libname='WORK' and

memname='HAVE'

;

quit;

*options compress=yes;

data need(keep=&list2 household);

declare hash ha(hashexp : 20,ordered : 'a');

declare hiter hi('ha');

ha.definekey('count');

ha.definedata('count',&list1 );

ha.definedone();

declare hash _ha(hashexp: 20,ordered : 'a');

_ha.definekey('key');

_ha.definedone();

do until(last);

set have end=last;

/*Remove obs which variable's are all missing firstly*/

if /*c*/nmiss(of &list2) lt &n then do;

count+1;

ha.add();

end;

length key $ 40;

array h{*} $ 40 &list2 ;

/*copy the first obs from Hash Table HA into PDV*/

_rc=hi.first();

do while(_rc eq 0); *until the end of Hash Table HA;

/*assign a unique cluster flag(i.e. household)*/

household+1;

do i=1 to &n;

/*push not missing value of current obs into another Hash Table _HA*/

if not missing(h{i}) then do;

key=h{i};

_ha.replace();

end;

/*start to run over Hash Table HA ,until can not find any more

observation which is the same cluster with current observation*/

do until(x=1);

x=1;

/*copy the first obs from Hash Table HA into PDV*/

rc=hi.first();

do while(rc=0);

found=0;

do j=1 to &n;

/*find whether any one of value is included in the current obs*/

key=h{j};

rcc=_ha.check();

if rcc =0 then found=1;

end;

if found then do;

/*if any one of value is included,then push the obs which is copied from

Hash Table HA into Hash Tables _HA,flag it the same cluster with the

current obs and output it into dataset*/

do k=1 to &n;

if not missing(h{k}) then do;

key=h{k};

_ha.replace();

end;

output;

x=0;

_count=count;*keep this found obs's index;

end;

rc=hi.next();

/*remove the found obs from Hash Table HA,since it has been seared*/

if found then rx=ha.remove(key : _count);

end;

/*clear up all the index which is the same cluster with the current obs*/

_ha.clear();

/*copy the first obs from Hash Table HA into PDV*/

_rc=hi.first();

end;

run;

data want;

set need;

by household;

length m $ 400;

retain m;

array x{*} $ &list2;

do i=1 to &n ;

if not find(m,strip(x{i})) and not missing(x{i}) then m=catx(' ',m,x{i});

end;

if last.household then do;

output;

call missing(m);

end;

keep household m;

run;

Re: Request for immediate assistance

Ksharp — Sun, 20 Apr 2014 01:46:03 GMT

Arthur.T,

Do you remember that question posted by sas_forum about clustering obs a year ago? You try to use proc format to get it ?

For Q.

1)I have no choice at least for now and my ability .But I can make it faster to transpose data . I am also glad to see someone get a better and faster code .

2)Yeah. You are right. I can get three of them in the same statement. I just want make it align neat .Hope not cost too much time.

Xia Keshan

Message was edited by: xia keshan

Re: Request for immediate assistance

PGStats — Sun, 20 Apr 2014 01:49:49 GMT

With SAS 9.1.3 you will have to rely on the skillful programming proposed, but with SAS/OR 12.1 you could use PROC OPTNET and request connected components, as follows:

data have;

infile datalines delimiter='|';

input subject1 subject2;

datalines;

2 | 4

2 | 5

2 | 6

2 | 8

4 | 7

4 | 11

6 | 9

6 | 10

6 | 12

10 | 15

10 | 16

13 | 14

16 | 17

;

proc optnet data_links=have out_nodes=want_long;

links_var from=subject1 to=subject2;

concomp;

run;

proc sort data=want_long; by concomp node; run;

data want;

length subjects $200;

do until (last.concomp);

set want_long; by concomp;

subjects = catx(" | ", subjects, node);

end;

keep subjects;

run;

proc print data=want noobs; run;

Re: Request for immediate assistance

Ksharp — Sun, 20 Apr 2014 02:09:46 GMT

Not realize still use 9.1

data have;
infile cards delimiter='|';
input subject1 $  subject2 $ ;
cards;
        2    |    4  
        2    |    5  
        2    |    6  
        2    |    8
        4    |    7  
        4    |    11  
        6    |    9  
        6    |    10
        6    |    12
        10  |    15  
        10  |    16  
        13  |    14  
        16  |    17
;
run;
proc sort data=have ;by subject1;run;
proc transpose data=have out=test(drop=_name_) ;
by subject1;
var subject2;
run;
proc sql noprint;
select quote(strip(name)),name,count(name) into : list1 separated by ',' , : list2 separated by ' ' , : n
 from dictionary.columns where libname='WORK' and memname='TEST';
quit;
 %put &list1  &list2 &n;


options compress=yes;
data want(keep=&list2 household);
declare hash ha(hashexp : 16);
declare hiter hi('ha');
ha.definekey('count');
ha.definedata('count',&list1 );
ha.definedone();
declare hash _ha(hashexp: 16);
_ha.definekey('key');
_ha.definedone();
do until(last);
set test end=last;
count+1;
ha.add();
end;
length key $ 40;
array h{*} $ 40 &list2 ;
_rc=hi.first();
do while(_rc eq 0); 
household+1;
do i=1 to &n;
if not missing(h{i}) then do; key=h{i}; _ha.replace();end;
end;
do until(x=1);
x=1;
rc=hi.first();
do while(rc=0);
found=0;
do j=1 to &n;
key=h{j};rcc=_ha.check();
if rcc =0 then found=1;
end;
if found then do;
do k=1 to &n;
if not missing(h{k}) then do;
key=h{k};_ha.replace();end;
end;
output;x=0; _count=count;
end;
rc=hi.next();
if found then rx=ha.remove(key : _count);
end;
end;
_ha.clear();
_rc=hi.first();
end;
run;
data final;
 set want;
 by household;
 length m $ 400;
 retain m;
 array x{*} $ &list2;
 do i=1 to &n ;
  if not indexw(m,strip(x{i})) and not missing(x{i}) then m=catx(' ',m,x{i});
 end;
 if last.household then do;output;call missing(m);end;
 keep household m;
run;

Xia Keshan

Message was edited by: xia keshan

Re: Request for immediate assistance

Ksharp — Sun, 20 Apr 2014 03:40:47 GMT

Tom,

With this dataset, I got a different result with mine.

2 4 2 5 2 6 2 8 4 7 4 11 6 9 6 10 6 12 10 15 10 16 13 14 16 17 99 4

Yours:

1 : 2 4 5 6 7 8 9 10 11 12 15 16 17

2 : 13 14

3 : 99

Mine:

1 : 2 4 5 6 7 8 9 10 11 12 15 16 17 99

2 : 13 14

Re: Request for immediate assistance

Tom — Sun, 20 Apr 2014 14:49:16 GMT

The part that grows the nets is assuming a directed graph. You can either change the logic that gets the list to include links that flow in the opposite direction. Or you can just expand the data to include both versions of the link.

data full;

set have ;

output;

x=from; from=to; to=x; output;

run;

Re: Request for immediate assistance

zana — Sun, 20 Apr 2014 15:48:31 GMT

Dear Ksharp,so thanks.

As i said, i use V9.1. Unfortunately by running your coed had some error (e.g., Unknown method CLEAR). Maybe by adding more information (or maybe by replacing input data with other suitable), you can better understand me.

Input data:

expected output:

Yours sincerely.

zana