[prev in list] [next in list] [prev in thread] [next in thread]
List: sas-l
Subject: Re: SAS Forum: Keep the columns that exist in another table
From: Roger DeAngelis <rogerjdeangelis () GMAIL ! COM>
Date: 2018-06-30 17:31:27
Message-ID: CAOUdXL_TOMFbeSFUKWK8R=xkUmgb5foQ0vZcGmNB=RTsBt5zug () mail ! gmail ! com
[Download RAW message or body]
Thanks Q and Mark
I think I have DOSUBitis, perhaps like Paul has Hashitis?
Nice additions will archive
Mark
Nice (out of the box)
Placing a variable before and after the variables in Sunday and then
keeping everything between the sentinels and dropping the sentinels on
output.
On Sat, Jun 30, 2018 at 12:46 PM, Keintz, Mark <mkeintz@wharton.upenn.edu>
wrote:
> This is why there are well-known rules for PDV construction in the data
> step:
>
> data sunday;
> set sashelp.class (keep=name age sex);
> where sex='F';
> run;
>
> data monday;
> set sashelp.class;
> where sex='M';
> run;
>
> data want(drop=_sentinel: );
> retain _sentinel1 .;
> if 0 then set sunday;
> retain _sentinel2 . ;
> set monday;
> keep _sentinel1 -- _sentinel2;
> run;
>
>
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
> Roger DeAngelis
> Sent: Saturday, June 30, 2018 9:07 AM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: SAS Forum: Keep the columns that exist in another table
>
> SAS Forum: Keep the columns that exist in another table
>
> github
> https://tinyurl.com/ybrd323o
> https://github.com/rogerjdeangelis/utl_keep_only_
> the_columns_that_exist_in_another_table
>
> On sunday I get a template dataset which has the variables I need to keep
> for the rest of the week.
> The name and type of extra variables on susequent days is not known.
>
> Inspired by
> https://tinyurl.com/y99e9sug
> https://communities.sas.com/t5/Base-SAS-Programming/How-
> to-drop-or-keep-variables-conditionally/m-p/474635
>
> Love the questions thta come from SAS Communties.
> Op is not stuck in box that I am stuck in.
>
>
> INPUT
> =====
>
> On Sunday I get the variables for the entire week;
>
> WORK.SUNDAY total obs=19
>
> DAY NAME AGE
>
> Sunday Alfred 14
> Sunday Alice 13
> Sunday Barbara 13
> Sunday Carol 14
> Sunday Henry 14
> Sunday James 12
> ...
>
> Typical Monday dataset
> Need to drop extra1 and extra2
> RULE
> ====
> WORK.MONDAY total obs=19 DROP THESE
> -----------------
> DAY NAME AGE EXTRA1 EXTRA2
>
> Monday Alfred 14 M 196
> Monday Alice 13 F 169
> Monday Barbara 13 F 169
> Monday Carol 14 F 196
> Monday Henry 14 M 196
> Monday James 12 M 144
> ...
>
>
> PROCESS
> =======
>
> data want;
> retain day;
> set monday(where=(0=
> %sysfunc(dosubl('
> proc sql; select name into :names separated by " "
> from sashelp.vcolumn where libname="WORK" and
> memname="SUNDAY";quit;
> '))));
> keep &names;
>
> run;quit;
>
>
> OUTPUT
> ======
>
> WORK.WANT total obs=19
>
> DAY NAME AGE
>
> Monday Alfred 14
> Monday Alice 13
> Monday Barbara 13
> Monday Carol 14
> Monday Henry 14
> ...
>
> * _ _ _
> _ __ ___ __ _| | _____ __| | __ _| |_ __ _
> | '_ ` _ \ / _` | |/ / _ \ / _` |/ _` | __/ _` |
> | | | | | | (_| | < __/ | (_| | (_| | || (_| |
> |_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_|
>
> ;
>
> data sunday;
> retain day;
> set sashelp.class(keep=name age);
> day="Sunday";
> run;quit;
>
> data monday;
> retain day;
> set sashelp.class(keep=name age sex age);
> day="Monday";
> extra1=sex;
> extra2=age*age;
> drop sex;
> run;quit;
>
> * _ _ _
> ___ ___ | |_ _| |_(_) ___ _ __
> / __|/ _ \| | | | | __| |/ _ \| '_ \
> \__ \ (_) | | |_| | |_| | (_) | | | |
> |___/\___/|_|\__,_|\__|_|\___/|_| |_|
>
> ;
> see process
>
[Attachment #3 (text/html)]
<div dir="ltr"><div class="gmail_default" \
style="font-family:monospace,monospace">Thanks Q and Mark</div><div \
class="gmail_default" style="font-family:monospace,monospace"><br></div><div \
class="gmail_default" style="font-family:monospace,monospace">I think I have \
DOSUBitis, perhaps like Paul has Hashitis?</div><div class="gmail_default" \
style="font-family:monospace,monospace"><br></div><div class="gmail_default" \
style="font-family:monospace,monospace">Nice additions will archive</div><div \
class="gmail_default" style="font-family:monospace,monospace"><br></div><div \
class="gmail_default" style="font-family:monospace,monospace">Mark</div><div \
class="gmail_default" style="font-family:monospace,monospace"><br></div><div \
class="gmail_default" style="font-family:monospace,monospace"> Nice (out of the \
box)</div><div class="gmail_default" \
style="font-family:monospace,monospace"><br></div><div class="gmail_default" \
style="font-family:monospace,monospace"> Placing a variable before and after the \
variables in Sunday and then</div><div class="gmail_default" \
style="font-family:monospace,monospace"> keeping everything between the sentinels \
and dropping the sentinels on output.</div></div><div class="gmail_extra"><br><div \
class="gmail_quote">On Sat, Jun 30, 2018 at 12:46 PM, Keintz, Mark <span \
dir="ltr"><<a href="mailto:mkeintz@wharton.upenn.edu" \
target="_blank">mkeintz@wharton.upenn.edu</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">This is why there are well-known rules for PDV construction \
in the data step: <br> <br>
data sunday;<br>
set sashelp.class (keep=name age sex);<br>
where sex='F';<br>
run;<br>
<br>
data monday;<br>
set sashelp.class;<br>
where sex='M';<br>
run;<br>
<br>
data want(drop=_sentinel: );<br>
retain _sentinel1 .;<br>
if 0 then set sunday;<br>
retain _sentinel2 . ;<br>
set monday;<br>
keep _sentinel1 -- _sentinel2;<br>
run;<br>
<div class="HOEnZb"><div class="h5"><br>
<br>
<br>
-----Original Message-----<br>
From: SAS(r) Discussion [mailto:<a \
href="mailto:SAS-L@LISTSERV.UGA.EDU">SAS-L@LISTSERV.UGA.EDU</a><wbr>] On Behalf Of \
Roger DeAngelis<br>
Sent: Saturday, June 30, 2018 9:07 AM<br>
To: <a href="mailto:SAS-L@LISTSERV.UGA.EDU">SAS-L@LISTSERV.UGA.EDU</a><br>
Subject: SAS Forum: Keep the columns that exist in another table<br>
<br>
SAS Forum: Keep the columns that exist in another table<br>
<br>
github<br>
<a href="https://tinyurl.com/ybrd323o" rel="noreferrer" \
target="_blank">https://tinyurl.com/ybrd323o</a><br> <a \
href="https://github.com/rogerjdeangelis/utl_keep_only_the_columns_that_exist_in_another_table" \
rel="noreferrer" target="_blank">https://github.com/<wbr>rogerjdeangelis/utl_keep_only_<wbr>the_columns_that_exist_in_<wbr>another_table</a><br>
<br>
On sunday I get a template dataset which has the variables I need to keep for the \
rest of the week.<br> The name and type of extra variables on susequent days is not \
known.<br> <br>
Inspired by<br>
<a href="https://tinyurl.com/y99e9sug" rel="noreferrer" \
target="_blank">https://tinyurl.com/y99e9sug</a><br> <a \
href="https://communities.sas.com/t5/Base-SAS-Programming/How-to-drop-or-keep-variables-conditionally/m-p/474635" \
rel="noreferrer" target="_blank">https://communities.sas.com/<wbr>t5/Base-SAS-Programming/How-<wbr>to-drop-or-keep-variables-<wbr>conditionally/m-p/474635</a><br>
<br>
Love the questions thta come from SAS Communties.<br>
Op is not stuck in box that I am stuck in.<br>
<br>
<br>
INPUT<br>
=====<br>
<br>
On Sunday I get the variables for the entire week;<br>
<br>
WORK.SUNDAY total obs=19<br>
<br>
DAY NAME AGE<br>
<br>
Sunday Alfred 14<br>
Sunday Alice 13<br>
Sunday Barbara 13<br>
Sunday Carol 14<br>
Sunday Henry 14<br>
Sunday James 12<br>
...<br>
<br>
Typical Monday dataset<br>
Need to drop extra1 and extra2<br>
RULE<br>
====<br>
WORK.MONDAY total obs=19 DROP THESE<br>
-----------------<br>
DAY NAME AGE EXTRA1 EXTRA2<br>
<br>
Monday Alfred 14 M 196<br>
Monday Alice 13 F 169<br>
Monday Barbara 13 F 169<br>
Monday Carol 14 F 196<br>
Monday Henry 14 M 196<br>
Monday James 12 M 144<br>
...<br>
<br>
<br>
PROCESS<br>
=======<br>
<br>
data want;<br>
retain day;<br>
set monday(where=(0=<br>
%sysfunc(dosubl('<br>
proc sql; select name into :names separated by " \
"<br>
from sashelp.vcolumn where libname="WORK" and \
memname="SUNDAY";quit;<br> '))));<br>
keep &names;<br>
<br>
run;quit;<br>
<br>
<br>
OUTPUT<br>
======<br>
<br>
WORK.WANT total obs=19<br>
<br>
DAY NAME AGE<br>
<br>
Monday Alfred 14<br>
Monday Alice 13<br>
Monday Barbara 13<br>
Monday Carol 14<br>
Monday Henry 14<br>
...<br>
<br>
* _ _ _<br>
_ __ ___ __ _| | _____ __| | __ _| |_ __ _<br>
> '_ ` _ \ / _` | |/ / _ \ / _` |/ _` | __/ _` |<br>
> > > > > > (_| | < __/ | (_| | (_| | || (_| |<br>
> _| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_|<br>
<br>
;<br>
<br>
data sunday;<br>
retain day;<br>
set sashelp.class(keep=name age);<br>
day="Sunday";<br>
run;quit;<br>
<br>
data monday;<br>
retain day;<br>
set sashelp.class(keep=name age sex age);<br>
day="Monday";<br>
extra1=sex;<br>
extra2=age*age;<br>
drop sex;<br>
run;quit;<br>
<br>
* _ _ _<br>
___ ___ | |_ _| |_(_) ___ _ __<br>
/ __|/ _ \| | | | | __| |/ _ \| '_ \<br>
\__ \ (_) | | |_| | |_| | (_) | | | |<br>
> ___/\___/|_|\__,_|\__|_|\___/<wbr>|_| |_|<br>
<br>
;<br>
see process<br>
</div></div></blockquote></div><br></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic