SAS/BASE question——answer library

tangyh

SAS版块现在设置 SAS/BASE SAS/SQL SAS/MACRO SAS/GRAPH四个帖子，每个帖子供大家把自己平时遇到的问题，和解决的方法以跟帖的形式发布出来，以方便后来人学习。

也可以是自己逛论坛时看到的比较好的解决问题的方法。

也可以是一些小技巧之类的。

为了方便阅读，希望不要有”谢谢。。。“ “好贴。。。”之类的跟帖。

如果要发表的不属于这四类，可以另建新帖。

Thanks.

tangyh

How do I permanently store formats in SAS?

To store formats to a library, use the library= option in your proc format statement. For example:

<br />
libname out "c:\somewhere";<br />
<br />
proc format library=out;<br />
<br />
...value statements...<br />
<br />
run;<br />

You can use these in the future by using the fmtsearch=(libname) option in an options statement. For example, in order to access the formats created above, you'd use:

<br />
libname out "c:\somewhere";<br />
<br />
options fmtsearch=(out);<br />
<br />
...programming statements...<br />

When SAS looks for formats, it automatically searches the work library. The fmtsearch= option tells SAS other libraries to find formats.

This method is an alternative to cutting and pasting relevant proc format code into every program where those particular formats are needed.

引自[quote]http://help.unc.edu/6213[/quote]

tangyh

How do I increase the memory available for my SAS jobs on the Research Computing server?

The default memory setting allows up to 1 GB. If you use the command:

<br />
  $ bsas -memsize max program_name.sas<br />

when submitting your batch SAS job, you will tell SAS it can use up to the maximum memory it is allowed to have by the compute node. If you need to run your SAS job on a machine that has more than 3 gigs of memory available, then you need to use the LSF command bsub to submit your SAS job and you need to specify the bsub resource option "-R p5aix" so that your job will be run on one of the high memory AIX UNIX machines available on Emerald:

<br />
    $  bsub  -R  p5aix  sas  -memsize  8g  program_name.sas  <br />

Note that options for bsub come before the word "sas" and options for SAS come after the word "sas".

tangyh

How do I print SAS graph files while working on the Research Computing server?

The general procedure is to:

    * Create a graphics file from SAS.

    * Send this file to a printer using the UNIX/Linux lp command.

Create a graphics file from SAS

To print a graphics file from SAS on Emerald, follow these steps:

   1. Specify an output fileref (i.e., a file name for the graphics output in a specified directory where you want to save the file).

   2. Set the goption "gsfname" equal to the fileref you designated in the previous step.

   3. Set the goption "device" equal to an appropriate PostScript device driver. (*For a complete list of SAS device drivers, use proc gdevice.) (e.g., applelw, ps300, or pscolor).

   4. Create your graphics using an appropriate SAS procedure (e.g., proc gplot, proc g3d, etc.).

   5. Print the graphics file. The UNIX lp command sends the graphics file to the printer.

The following SAS program illustrates how to save a SAS graph (created from the data set, CLASS, from the SASUSER library) as a graphics stream file (.gsf ):

<br />
filename  outsas  '~/sasgraph.gsf' ; /*  Output fileref  */ <br />
<br />
goptions  device=applelw             /*  Generic postscript device */<br />
<br />
       gsfname=outsas                /*  Fileref for output file <br />
<br />
                                      *    must be on prior FILENAME  */ <br />
<br />
       gsfmode=replace;              /*  Write over old output file */ <br />
<br />
proc gplot data=sasuser.class;       /*  Create graphics  */ <br />
<br />
   plot weight*height; <br />
<br />
run; <br />
<br />
quit;<br />

Send this file to a printer using the UNIX/Linux command

Now send the graphics file to the printer. For example, at the Unix/Linux prompt, use the following code to print the graph sasgraph.gsf, on the printer in the IRSS lab, lp48:

<br />
$ lp -d lp48 sasgraph.gsf<br />

Read more about Printing from Research Computing servers.

tangyh

sas中work下的多个表批量导成excel表

<br />
%macro dump(file, where);<br />
PROC EXPORT<br />
DATA=&file<br />
OUTFILE="&where.\&file"<br />
DBMS=excel REPLACE;<br />
RUN;<br />
%mend;<br />
data _null_;<br />
set sashelp.vtable;<br />
call execute('%dump('||memname||', c:\ExcelTable)');<br />
where libname='WORK';<br />
run;<br />

tangyh

[quote]引用楼主yiyi于2009-09-15 20:36发表的 sas 窗口显示output　window full :

我处理红外数据，数据比较多，想在sas输出窗口中输出数据　程序运行时会显示出output　window full,

用PROC PRINCOMP OUT=llb.new001 OUTSTAT=llb.new002 PREFIX=P N=1595 STANDARD;

VAR x1-x1595;

RUN;

这段程序把结果输出到外部，发现结果不完全，怎么回事？怎么处理？

.......

[/quote]

这里提供三种解决方案

一种是将log和output转移。

你可以用下面的命令关闭listing输出。

<br />
ODS LISTING CLOSE.<br />

然后用ods将output输出到txt或者rtf、pfd。

可以用下面的语句将log输出。

<br />
proc printto log="c:\yourfilename.log";<br />
run;<br />

另外一种是：

你可以有选择的在你的程序中加入下面的语句：

DM 'Clear log' ;

DM 'Clear Out' ;

第三种：

在v9中有两个命令：

DMSOUTSIZE= n | nK | hexX | MIN | MAX

DMSLOGSIZE= n | nK | hexX | MIN | MAX

你可以试一下。

tangyh

以下摘自西铁房临床试验爱好者网 http://www.clinicaltrialfans.com/forums/list.page

[quote]

各位好！

不知道SAS中能不能快速的把字符型变量转换成数字型的变量。

接到的数据库不太规范，一律都是字符型的，应该如何批量的转换其变量的类型？总变量有几千个！

很困惑！希望大家帮忙！

[/quote]

[quote]

可以给你提供一种思路，首先通过系统字典数据表获取要操作数据集的变量列表及其数据类型，或者直接通过SAS函数VARNUM，VARNAME及VARTYPE等来获取数据集所有变量列表及其数据类型；或者更简单一点采用系统变量_character_来操作。简单示例代码如下：

data xx;<br />
    input a $20.;<br />
    b=a;<br />
    c=a;<br />
    d=a;<br />
    cards;<br />
    12<br />
    236    <br />
    234.3<br />
    4324434<br />
    23423.34<br />
    ;<br />
run;<br />
<br />
data xx2;<br />
    set xx;<br />
    array chrvar(*) _character_;<br />
    array newvar(4) ;<br />
    do i=1 to dim(chrvar);<br />
        if not missing(chrvar(i)) then newvar(i) = input(chrvar(i),best12.);<br />
    end;<br />
    drop _character_;<br />
run;

上述代码没有考虑变量名称的问题，直接以新变量newvar1, newva2, ...代替，另外也要求需要转换的原始变量数据规范，不然会有缺失值。

希望上述示例能对你有所帮助。

[/quote]

tangyh

批量导出work library下的数据集。

<br />
*Output the names of all datasets in work library to a dataset;<br />
ods output members=name;<br />
ods listing close;<br />
proc datasets lib=work MEMTYPE=data;<br />
quit;<br />
ods output close;<br />
ods listing;<br />
<br />
*Create macro variables to save these filenames;<br />
data _null_;<br />
  set name end=last;<br />
  call symput ('file'||strip(put(num,8.)),name);<br />
  if last then call symput ('file_num',num);<br />
run;<br />
<br />
*Output excel files;<br />
%macro output;<br />
%do i= 1 %to &file_num;<br />
proc export data=&&file&i<br />
  outfile="c:\&&file&i..xls"<br />
  DBMS=excel replace;<br />
run;<br />
%end;<br />
%mend;<br />
<br />
%output<br />

another one

<br />
%macro dump(file, where);<br />
PROC EXPORT<br />
DATA=&file<br />
OUTFILE="&where.\&file"<br />
DBMS=excel REPLACE;<br />
RUN;<br />
%mend;<br />
data _null_;<br />
set sashelp.vtable;<br />
call execute('%dump('||memname||', c:\ExcelTable)');<br />
where libname='WORK';<br />
run; <br />

tangyh

Program 1.4: Program to capitalize the first letter of the first and last

name (using SUBSTR)

<br />
***Primary functions: LOWCASE, UPCASE<br />
***Other function: SUBSTR (used on the left and right side of the equal sign);<br />
<br />
DATA CAPITALIZE;<br />
   INFORMAT FIRST LAST $30.;<br />
   INPUT FIRST LAST;<br />
   FIRST = LOWCASE(FIRST);<br />
   LAST = LOWCASE(LAST);<br />
   SUBSTR(FIRST,1,1) = UPCASE(SUBSTR(FIRST,1,1));<br />
   SUBSTR(LAST,1,1) = UPCASE(SUBSTR(LAST,1,1));<br />
DATALINES;<br />
ronald cODy<br />
THomaS eDISON<br />
albert einstein<br />
;<br />
PROC PRINT DATA=CAPITALIZE NOOBS;<br />
   TITLE "Listing of Data Set CAPITALIZE";<br />
RUN;<br />
<br />

tangyh

Program 1.5: Capitalizing the first letter of each word in a string

<br />
***Primary function: PROPCASE;<br />
<br />
DATA PROPER;<br />
   INPUT NAME $60.;<br />
   NAME = PROPCASE(NAME);<br />
DATALINES;<br />
ronald cODy<br />
THomaS eDISON<br />
albert einstein<br />
;<br />
PROC PRINT DATA=PROPER NOOBS;<br />
   TITLE "Listing of Data Set PROPER";<br />
RUN;<br />

tangyh

转自：

http://www.clinicaltrialfans.com/posts/list/576.page

Program 1.6: Alternative program to capitalize the first letter of each

word in a string

<br />
***First and last name are two separate variables.<br />
<br />
DATA PROPER;<br />
   INFORMAT FIRST LAST $30.;<br />
   INPUT FIRST LAST;<br />
   LENGTH NAME $ 60;<br />
   CALL CATX(' ', NAME, FIRST, LAST);<br />
   NAME = PROPCASE(NAME);<br />
DATALINES;<br />
ronald cODy<br />
THomaS eDISON<br />
albert einstein<br />
;<br />
PROC PRINT DATA=PROPER NOOBS;<br />
   TITLE "Listing of Data Set PROPER";<br />
RUN;<br />

tangyh

转自：

http://www.clinicaltrialfans.com/posts/list/576.page

Program 1.7: Using the COMPBL function to convert multiple blanks to a

single blank

<br />
***Primary function: COMPBL;<br />
<br />
DATA SQUEEZE;<br />
   INPUT #1 @1  NAME    $20.<br />
         #2 @1  ADDRESS $30.<br />
         #3 @1  CITY    $15.<br />
            @20 STATE    $2.<br />
            @25 ZIP      $5.;<br />
   NAME = COMPBL(NAME);<br />
   ADDRESS = COMPBL(ADDRESS);<br />
   CITY = COMPBL(CITY);<br />
DATALINES;<br />
RON CODY<br />
89 LAZY BROOK ROAD<br />
FLEMINGTON         NJ   08822<br />
BILL     BROWN<br />
28   CATHY   STREET<br />
NORTH   CITY       NY   11518<br />
;<br />
PROC PRINT DATA=SQUEEZE;<br />
   TITLE 'Listing of Data Set SQUEEZE';<br />
   ID NAME;<br />
   VAR ADDRESS CITY STATE ZIP;<br />
RUN;<br />

tangyh

Program 1.10: Counting the number of numerals in a string

<br />
***Primary functions: COMPRESS, LENGTHN;<br />
<br />
DATA COUNT;<br />
   INPUT STRING $20.;<br />
   ONLY_LETTERS = COMPRESS(STRING,'0123456789');<br />
   NUM_NUMERALS = LENGTHN(STRING) - LENGTHN(ONLY_LETTERS);<br />
DATALINES;<br />
ABC123XYZ<br />
XXXXX<br />
12345<br />
1234X<br />
;<br />
PROC PRINT DATA=COUNT NOOBS;<br />
   TITLE "Listing of Data Set COUNT";<br />
RUN;<br />

tangyh

Program 1.45: Converting values of '1','2','3','4', and '5' to 'A','B','C','D', and

'E' respectively

<br />
***Primary function: TRANSLATE;<br />
<br />
DATA MULTIPLE;<br />
   INPUT QUES : $1. @@;<br />
   QUES = TRANSLATE(QUES,'ABCDE','12345');<br />
DATALINES;<br />
1 4 3 2 5<br />
5 3 4 2 1<br />
;<br />
PROC PRINT DATA=MULTIPLE NOOBS;<br />
   TITLE "Listing of Data Set MULTIPLE";<br />
RUN;<br />

tangyh

问个SAS output的问题

使用了proc mixed, 生成predicted value之后怎样把结果output成一个sas data set

，以方便以后使用predicted这个variable。查过没有output statement。

在其他proc,比如proc reg，proc glm中是不是做法也该差不多？

具体的就是

<br />
proc mixed;<br />
model .../outpred=YourData;<br />
run;<br />

tangyh

Hi Everyone,

I hope I am able to describe my question adequately enough to solicit some suggestions. I am currently working with survey data which has Census data appended via the respondents' tract numbers. I am trying to create tract-level measures of residents' attitudes by aggregating up the scores of respondents within the same census tracts. Currently the data look something like this:

Resp ID            Census Tract               Attitude

1                     10001                         3.0

2                     10001                         2.5

3                     10001                         4.2

4                     10001                         3.5

5                     10002                         2.2

6                     10002                         4.0

7                     10002                         3.3

The simple method would be to run a mean on Attitude by census tract, output that into a dataset, and then merge it back into the survey data, matching on census tract number. That way, each respondent would be assigned the mean attitude score of his/her census tract.

HOWEVER, what I really need is to assign each respondent the mean attitude that *excludes* his/her own individual attitude. So for respondent 1, he should be assigned the mean of respondents' 2-4 scores; respondent 2 would get the mean of respondents' 1, 3, and 4, and so on. The number of respondents per tract varies. Currently I have 851 respondents nested within 77 census tracts.

Any ideas are much appreciated.

Thank you,

解答：

One idea that comes to mind is this.

Find the sum of the attitues for each tract, and find the no. of respondents in each tract. Both can be achieved by doing a simple PROC MEANS (you will need to output the sum and “n”, for every tract). Merge those by tract no on your dataset. Then you create a variable called S= (sum-attitude) / (n-1). That will give you the mean attitude for each respondent excluding his/her attitude.

To summarize, your dataset will now look like this:

Resp ID            Census Tract               Attitude          sum         n             S

1                     10001                         3.0                 13.2         4            3.4

2                     10001                         2.5                 13.2         4            3.567

3                     10001                         4.2                 13.2         4

4                     10001                         3.5                 13.2         4

5                     10002                         2.2                  9.5          3

6                     10002                         4.0                  9.5         3

7                     10002                         3.3                  9.5         3

tangyh

问题：

字符型变量求缺失值得个数

用proc means可以很方便的得到数值型变量missing value的个数；

对于包含有多个字符型变量的数据集，各位大侠有没有什么好的方法产生一个数据集，该数据集包含字符型变量的变量名以及缺失值得个数？

解答一：

<br />
    data b;<br />
    set a(keep=_char_);<br />
    array mychar(*) $ _character_;<br />
      do i=1 to dim(mychar);<br />
        if  mychar(i) ="" then mychar{i}=.;<br />
          else mychar(i)=1;<br />
      end;<br />
    run;<br />
<br />
    proc means....<br />

解答二：

<br />
    data urData;<br />
       x=''; y ='a'; output;   x='q'; y='b'; output;   x=''; y =''; output;<br />
    run;<br />
    data missingSummary(keep =missingVs); set urData;<br />
       array mAry $ _character_; length missingVs $ 32;<br />
       do over mAry;<br />
          if missing(mAry) then do;<br />
             missingVs =vname(mAry); output;<br />
          end;<br />
       end;   <br />
    proc freq noprint;<br />
       table  missingVs/out =MissingFreqs(keep =missingVs count);<br />
    run;<br />

tangyh

问题：

程序如下：

data temp;

input x@@;

cards;

1 2 2 3 3 3 4 4 4 4 5 5 5 5 5

;

run;

proc format;

value fmt

1='1'

4-5='4-5'

2-3='2-3';

run;

proc means data=temp noprint missing;

var x;

class x/mlf;

format x fmt.;

output out=rst

sum=/autoname;

run;

将RST导出为EXCEL后我希望x的顺序与fmt一致，即1,4-5,2-3而不是1,2-3,4-5

用什么命令能够实现?

解答：

1. FORMAT定义加notsorted

2. PROC MEANS加order=data

3. CLASS statement加preloadfmt

tangyh

请教;如何筛选出每个月最后一天

例如有以下数据集

rq

1997-10-6

1997-10-7

1997-10-8

1997-10-9

1997-10-10

1997-10-13

1997-10-14

1997-10-15

1997-10-16

1997-10-17

1997-10-20

1997-10-21

1997-10-22

1997-10-23

1997-10-24

1997-10-27

1997-10-28

1997-10-29

1997-10-30

1997-10-31

1997-11-3

1997-11-4

1997-11-5

1997-11-6

1997-11-7

1997-11-10

1997-11-11

1997-11-12

1997-11-13

1997-11-14

1997-11-17

1997-11-18

1997-11-19

1997-11-20

1997-11-21

1997-11-24

1997-11-25

1997-11-26

1997-11-27

1997-11-28

1997-12-1

1997-12-2

1997-12-3

1997-12-4

1997-12-5

1997-12-8

1997-12-9

1997-12-10

1997-12-11

1997-12-12

1997-12-15

1997-12-16

1997-12-17

1997-12-18

1997-12-19

1997-12-22

1997-12-23

1997-12-24

1997-12-25

1997-12-26

1997-12-29

1997-12-30

1997-12-31

1998-1-5

1998-1-6

1998-1-7

1998-1-8

1998-1-9

1998-1-12

1998-1-13

1998-1-14

1998-1-15

1998-1-16

1998-1-19

1998-1-20

1998-1-21

1998-1-22

1998-1-23

1998-2-9

1998-2-10

1998-2-11

1998-2-12

1998-2-13

1998-2-16

1998-2-17

1998-2-18

1998-2-19

1998-2-20

1998-2-23

1998-2-24

1998-2-25

1998-2-26

1998-2-27

1998-3-2

1998-3-3

1998-3-4

1998-3-5

1998-3-6

1998-3-9

1998-3-10

1998-3-11

1998-3-12

1998-3-13

1998-3-31

rq表示日期，

现在想筛选出每个月的最后一天，请问该如何编程？谢谢

也就是得到以下数据集

1997-10-31

1997-11-28

1997-12-31

1998-1-23

1998-2-27

1998-3-31

解答：

weimomei »

方法之一：

假如你的数据集名称a1:

<br />
data a2;<br />
set a1;<br />
year=year(cdate);<br />
month=month(cdate);<br />
run;<br />
proc sort data=a2;<br />
by year month cdate;<br />
run;<br />
data a3;<br />
set a2;<br />
by year month cdate;<br />
if last.month then output;<br />
run; <br />

byes »

方法一显然不是最优方案

比较繁琐

<br />
horse1 » <br />
<br />
    data a2;<br />
         set a1;<br />
        rt=input(rq,yymmdd10.);<br />
        yr=put(rt,yymm7.);<br />
    run;<br />
<br />
    proc sql;<br />
         create table a3 as<br />
        select max(rt) format=yymmdd10. as last<br />
        from a2<br />
        group by yr;<br />
    quit;<br />

byes »

咳。。。。。。。。。。。

林嘉祥 »

<br />
    data b(keep=rq);<br />
        set a1 (keep=rq rename=(rq=cur_rq)) end=eof;<br />
        rq = lag(cur_rq);<br />
        if _n_>1 then<br />
             if intnx('month', rq, 1, 'beginning') le cur_rq then<br />
                 output;<br />
        if eof then do;<br />
             rq = cur_rq;<br />
             output;<br />
        end;<br />
    run;<br />
<br />

it just has as many steps as LSD did.

superkuhasu »

<br />
    proc sort data=a1;<br />
    by descending rq;/*选第一天就不用sort了*/<br />
    run;<br />
    data a2;<br />
    set a1;<br />
    if dif(month(rq)) ne 0;<br />
    run;<br />

林嘉祥 »

<br />
    proc sql;<br />
    select rq from(<br />
        select max(rq) as rq format=yymmdd10., month(rq) as month, year(rq) as year<br />
             from a<br />
             group by CALCULATED year, CALCULATED month<br />
        );<br />
    quit;<br />
<br />

<br />
    proc report data=a nowd out=b(keep=rq);<br />
            column rq rq=month;<br />
            define rq/max format=yymmdd10.;<br />
            define month/group format=yymmn6.;<br />
    run;<br />

byes »

兄弟，真是强悍啊。

继续

sql用一个select就可以了，不用嵌套select

林嘉祥 »

真的不知道怎么办了，byes别折磨人了。料就这么多，要不为什么上面的怎么搞都没事，咱家只是拉了拉幼齿就栽了呢？

<br />
    proc means data=a noprint max;<br />
    class rq;<br />
    var rq;<br />
    format rq yymmS7.;<br />
    ways 1;<br />
    output out=xxx(drop=_type_ _freq_) max=max_rq;<br />
    run;<br />
<br />

<br />
    proc univariate data=a noprint;<br />
    class rq;<br />
    var rq;<br />
    format rq yymms7.;<br />
    output out=xxx max=max_rq;<br />
    run;<br />

or if it's sorted then

<br />
    data xxx;<br />
    set a;<br />
    by rq groupformat;<br />
    if last.rq;<br />
    format rq yymms7.;<br />
    run;<br />

byes »

上面最有一个data步正是我想要的。牛人。

五体投地

林嘉祥 »

byes过奖了！跟您的指导是分不开的。

最近咱家名气实在是太大了点。不就说了几句真话嘛！我这人最大的缺点就是敢讲真话，用你们喝过洋墨水的人的话说就是“too outspoken”（别误会，我是正宗的工农兵大学生，这词儿是前几年听江总说的）。咱们共产党员说真话就要做好被误解的准备，尤其是网上一帮别有用心的人总是喜欢煽动不明真相的群众，给我们机关造成了不小的压力。这不，领导上暂时解除了我的职务，安排咱学ＳＡＳ来了！

syyw

太感谢楼主了，困惑很久的许多问题终于找到了答案！！