[prev in list] [next in list] [prev in thread] [next in thread]
List: sas-l
Subject: Re: combine datasets vertically, retain only common variables
From: Sigurd Hermansen <SigurdHermansen () WESTAT ! COM>
Date: 2020-09-27 23:43:46
Message-ID: 436027d4189449d593834e8f51a1c9ac () westat ! com
[Download RAW message or body]
[Attachment #2 (text/plain)]
Mark:
As a fellow time series analyst, I agree that data steps supplement SAS Proc SQL in \
critical ways. Sequencing of data beyond first and last of ordered groups, \
connections to hash indexes, and building test data sets works best in data steps. I \
would remind everyone that SAS PROC SQL also creates views that can serve as \
templates. For example, a CREATE VIEW X statement creates a view to which executions \
of SAS procedures on randomly selected subsets append, say, classifications or \
estimates from statistical models. S
From: Keintz, Mark <mkeintz@wharton.upenn.edu>
Sent: Sunday, September 27, 2020 12:03 AM
To: Sigurd Hermansen <SigurdHermansen@westat.com>; SAS-L@LISTSERV.UGA.EDU
Subject: RE: combine datasets vertically, retain only common variables
CAUTION: External Email *
And if you need to use a data step for certain types of programming unavailable in \
sql (say lags, dow groups, hashes, etc.), use the SQL to make a template, then tweak \
the data step to use the template to process the original datasets.
options obs=0;
proc sql;
create table tmplat as
select * from
(select * from dsn1) union corr
(select * from dsn2) union corr
(select * from dsn3) ;
quit;
options obs=max;
data want;
if 0 then set tmplat;
retain _s1 . ;
set dsn1 dsn2 dsn3 ;
retain _s2 . ;
drop _s1 -- _s2;
*** other code here ***;
run;
Regards,
Mark
-----Original Message-----
From: SAS(r) Discussion <SAS-L@LISTSERV.UGA.EDU<mailto:SAS-L@LISTSERV.UGA.EDU>> On \
Behalf Of S
Sent: Wednesday, September 23, 2020 8:00 PM
To: SAS-L@LISTSERV.UGA.EDU<mailto:SAS-L@LISTSERV.UGA.EDU>
Subject: Re: combine datasets vertically, retain only common variables
WCW:
Why limit yourself to data steps. The PROC SQL UNION CORRESPONDIND seems made for \
this problem:
/* Simplest example. */
data dsn1;
x=123;
y=234;
z=345;
run;
data dsn2;
a=123;
y=234;
z=345;
run;
data dsn3;
x=123;
y=234;
b=345;
run;
proc sql;
create table want as
select * from
(select * from dsn1)
union corr
(select * from dsn2)
union corr
(select * from dsn3)
;
quit;
Should a column variable have different types in different data sets, you can convert \
it in one of the select statements. No need to worry about the order of variables in \
select statements.
S
* Please use caution when responding and/or clicking on links as this email \
originated from outside of Westat.
[Attachment #3 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"> <head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"SAS Monospace";
panose-1:2 11 6 9 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
{mso-style-priority:99;
mso-style-link:"Plain Text Char";
margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.PlainTextChar
{mso-style-name:"Plain Text Char";
mso-style-priority:99;
mso-style-link:"Plain Text";
font-family:"Calibri",sans-serif;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:#1F497D">Mark:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">As a fellow time series analyst, I \
agree that data steps supplement SAS Proc SQL in critical ways. Sequencing of data \
beyond first and last of ordered groups, connections to hash indexes, and building \
test data sets works best in data steps. I would remind everyone that SAS PROC SQL \
also creates views that can serve as templates. For example, a CREATE VIEW X \
statement creates a view to which executions of SAS procedures on randomly selected \
subsets append, say, classifications or estimates from statistical models. \
<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#1F497D">S<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="color:#1F497D"><o:p> </o:p></span></p> <div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b>From:</b> Keintz, Mark <mkeintz@wharton.upenn.edu> <br>
<b>Sent:</b> Sunday, September 27, 2020 12:03 AM<br>
<b>To:</b> Sigurd Hermansen <SigurdHermansen@westat.com>; \
SAS-L@LISTSERV.UGA.EDU<br> <b>Subject:</b> RE: combine datasets vertically, retain \
only common variables<o:p></o:p></p> </div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div style="border:dotted crimson 1.0pt;padding:2.0pt 2.0pt 2.0pt 2.0pt">
<p class="MsoNormal"><b><span \
style="font-size:9.0pt;font-family:"Arial",sans-serif;color:crimson">CAUTION: \
External Email</span></b><span \
style="font-size:9.0pt;font-family:"Arial",sans-serif;color:crimson"> *<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span \
style="font-size:4.5pt;font-family:"Arial",sans-serif"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoPlainText">And if you need to use a data step for certain types of \
programming unavailable in sql (say lags, dow groups, hashes, etc.), use the SQL to \
make a template, then tweak the data step to use the template to process the original \
datasets. <o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">options</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> obs=</span><b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:teal;background:white">0</span></b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white">;<o:p></o:p></span></p> <p \
class="MsoNormal" style="text-autospace:none"><b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:navy;background:white">proc</span></b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> </span><b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:navy;background:white">sql</span></b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white">;<o:p></o:p></span></p> <p \
class="MsoNormal" style="text-autospace:none"><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> </span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">create</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> </span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">table</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> tmplat </span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">as</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"><o:p></o:p></span></p> <p \
class="MsoNormal" style="text-autospace:none"><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> </span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">select</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> * </span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">from</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> <o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> (</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">select</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white">
* </span><span style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">from</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> dsn1) </span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">union</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> corr<o:p></o:p></span></p> <p \
class="MsoNormal" style="text-autospace:none"><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> (</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">select</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white">
* </span><span style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">from</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> dsn2) </span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">union</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> corr<o:p></o:p></span></p> <p \
class="MsoNormal" style="text-autospace:none"><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> (</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">select</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white">
* </span><span style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">from</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> dsn3) ;<o:p></o:p></span></p> <p \
class="MsoNormal" style="text-autospace:none"><b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:navy;background:white">quit</span></b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white">;<o:p></o:p></span></p> <p \
class="MsoNormal" style="text-autospace:none"><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">options</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> obs=max;<o:p></o:p></span></p> <p \
class="MsoNormal" style="text-autospace:none"><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"><o:p> </o:p></span></p> <p \
class="MsoNormal" style="text-autospace:none"><b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:navy;background:white">data</span></b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> want;<o:p></o:p></span></p> <p \
class="MsoNormal" style="text-autospace:none"><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> </span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">if</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> </span><b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:teal;background:white">0</span></b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> </span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">then</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> </span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">set</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> tmplat;<o:p></o:p></span></p> <p \
class="MsoNormal" style="text-autospace:none"><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> </span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">retain</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> _s1 </span><b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:teal;background:white">.</span></b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> ;<o:p></o:p></span></p> <p \
class="MsoNormal" style="text-autospace:none"><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> </span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">set</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> dsn1 dsn2 dsn3 ;<o:p></o:p></span></p> \
<p class="MsoNormal" style="text-autospace:none"><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> </span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">retain</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> _s2 </span><b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:teal;background:white">.</span></b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> ;<o:p></o:p></span></p> <p \
class="MsoNormal" style="text-autospace:none"><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> </span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:blue;background:white">drop</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> _s1 -- _s2;<o:p></o:p></span></p> <p \
class="MsoNormal" style="text-autospace:none"><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"><o:p> </o:p></span></p> <p \
class="MsoNormal" style="text-autospace:none"><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"> </span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:green;background:white">*** other code here ***;</span><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white"><o:p></o:p></span></p> <p \
class="MsoPlainText"><b><span style="font-size:14.0pt;font-family:"SAS \
Monospace";color:navy;background:white">run</span></b><span \
style="font-size:14.0pt;font-family:"SAS \
Monospace";color:black;background:white">;</span><o:p></o:p></p> <p \
class="MsoPlainText"><o:p> </o:p></p> <p \
class="MsoPlainText">Regards,<o:p></o:p></p> <p \
class="MsoPlainText">Mark<o:p></o:p></p> <p class="MsoPlainText">-----Original \
Message-----<br>
From: SAS(r) Discussion <<a \
href="mailto:SAS-L@LISTSERV.UGA.EDU">SAS-L@LISTSERV.UGA.EDU</a>> On Behalf Of \
S<br>
Sent: Wednesday, September 23, 2020 8:00 PM<br>
To: <a href="mailto:SAS-L@LISTSERV.UGA.EDU">SAS-L@LISTSERV.UGA.EDU</a><br>
Subject: Re: combine datasets vertically, retain only common variables<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">WCW:<o:p></o:p></p>
<p class="MsoPlainText">Why limit yourself to data steps. The PROC SQL UNION \
CORRESPONDIND seems made for this problem:<o:p></o:p></p> <p class="MsoPlainText">/* \
Simplest example. */<o:p></o:p></p> <p class="MsoPlainText">data dsn1;<o:p></o:p></p>
<p class="MsoPlainText">x=123;<o:p></o:p></p>
<p class="MsoPlainText">y=234;<o:p></o:p></p>
<p class="MsoPlainText">z=345;<o:p></o:p></p>
<p class="MsoPlainText">run;<o:p></o:p></p>
<p class="MsoPlainText">data dsn2;<o:p></o:p></p>
<p class="MsoPlainText">a=123;<o:p></o:p></p>
<p class="MsoPlainText">y=234;<o:p></o:p></p>
<p class="MsoPlainText">z=345;<o:p></o:p></p>
<p class="MsoPlainText">run;<o:p></o:p></p>
<p class="MsoPlainText">data dsn3;<o:p></o:p></p>
<p class="MsoPlainText">x=123;<o:p></o:p></p>
<p class="MsoPlainText">y=234;<o:p></o:p></p>
<p class="MsoPlainText">b=345;<o:p></o:p></p>
<p class="MsoPlainText">run;<o:p></o:p></p>
<p class="MsoPlainText">proc sql;<o:p></o:p></p>
<p class="MsoPlainText"> create table want as<o:p></o:p></p>
<p class="MsoPlainText"> select * from <o:p></o:p></p>
<p class="MsoPlainText"> (select * from dsn1)<o:p></o:p></p>
<p class="MsoPlainText"> union corr<o:p></o:p></p>
<p class="MsoPlainText"> (select * from dsn2)<o:p></o:p></p>
<p class="MsoPlainText"> union corr<o:p></o:p></p>
<p class="MsoPlainText"> (select * from dsn3)<o:p></o:p></p>
<p class="MsoPlainText"> ;<o:p></o:p></p>
<p class="MsoPlainText">quit;<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">Should a column variable have different types in different \
data sets, you can convert it in one of the select statements. No need to worry about \
the order of variables in select statements. <o:p></o:p></p>
<p class="MsoPlainText">S<o:p></o:p></p>
</div>
<div style="border:dotted crimson 1.0pt;padding:2.0pt 2.0pt 2.0pt 2.0pt">
<p class="MsoNormal"><span \
style="font-size:9.0pt;font-family:"Arial",sans-serif">* Please use \
caution when responding and/or clicking on links as this email originated from \
outside of Westat. <o:p></o:p></span></p>
</div>
</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic