Murray_Court Tracker

Re: if/and/or Statements

Murray_Court — Sat, 22 Mar 2014 01:09:14 GMT

The syntax is exactly as you described.

data want;

set have;

if code1=1 AND (ind_code in ("AG","FOOD") OR sic_code in (78,98,47) ) then reg = 1;

else reg=0;

run;

-Murray.

Re: Trend function in SAS

Murray_Court — Fri, 21 Mar 2014 10:25:25 GMT

A linear regression would give you the answer based on a linear trendline..

Proc reg data=have;

model units = date;

run;

This will give you gradient and intercept coefficients that you can apply to the date (in sas date form) to give you a linear estimate.

This code and be emended to include polynomial trend-line fitting as shown to account for higher order relationships:

Proc reg data=have;

model units = date date*date;

run;

Re: Need a Macro to update the values in the datastep automatically from an excel sheet

Murray_Court — Fri, 21 Mar 2014 09:19:14 GMT

If the excel sheet is in a standard format you can use a proc import step.

proc import datafile="path\spreadsheet.xlsx" out=want dbms=csv replace;

getnames=yes;

run;

Re: Single observation per subject from multiple observation per subject

Murray_Court — Thu, 20 Mar 2014 09:59:46 GMT

summarize using sql.

proc sql;

create table want as

select id, 1 as seq,

max(a) as a, /* or sum(a) as a, whichever appropriate */

max(b) as b,

max(c) as c

from have

group by id, seq;

quit;

Re: Iterative Macro Variable code

Murray_Court — Wed, 19 Mar 2014 16:19:49 GMT

I think you are missing an &,

ibname NACO 'sas/data/reference';

%macro NACO (ext=);

Proc Sql;

Create Table naco as select

Select count(*) from naco.NAco_&ext.;

quit;

%Mend NACO;

%NACO (ext=201302);

Re: Does SAS have function to get "Skewness" directly

Murray_Court — Sat, 15 Mar 2014 09:52:59 GMT

I believe proc Univariate gives Skewness statistics for a given variable.

Re: Proc freq - calculating percentages AFTER excluding certain counts

Murray_Court — Wed, 29 Jan 2014 07:31:47 GMT

SQL is your friend here.

Pro sql;

create table want as select

age, sum(case when gender="M" then 1 else 0 end) as M, sum(case when gender="F" then 1 else 0 end) as F

from have

group by age

having (M ge 1 or F ge 1)

;

quit;

Apologies for possible syntax errors as I am at home.

Re: CATS (X) not starting with 111

Murray_Court — Tue, 28 Jan 2014 09:22:52 GMT

If you want to select any record which does not begin with '111' then use the folling code;

Data want;

set have;

y=put(a,8.);

z=substr(y,1,3);

if z="111" then delete;

run;

Re: SAS dataset loading into sql server

Murray_Court — Mon, 27 Jan 2014 16:16:21 GMT

Perhaps you have an asymmetrical network conntection that means that you can download data much faster than you can upload it? This is very common.

Re: Wrong data step to flag data

Murray_Court — Mon, 27 Jan 2014 16:13:50 GMT

It looks like you are only actually merging on proc1 from your example.

SQL should do the trick.

proc sql;

create table want a select a.*, b.proc1 as tbl1_proc1, b.proc2 as tbl1_proc2

from

have1 as b

inner join

have2 as a

a.proc1=b.proc1

;

quit;

Re: How to make imported SAS variable name conform to convention

Murray_Court — Thu, 23 Jan 2014 20:51:28 GMT

Its very messy, but here is what I would try:

1. Proc import with obs = 1 without retrieving the names, making a small dataset with just the names.

2. Act upon the dataset with a macro that inspects each character of each title individually and switches it if it doesn't conform (regular expressions would be go for this)

3. Select our names into a macro variable using proc sql

4. proc import with startobs = 2 to pull just the data from the file.

5. use a small macro that renames the automatically generated names with our formatted names stored in our macro variable from step 3.

Anyone have any better ideas?

Re: Deleting rows in a final dataset?

Murray_Court — Wed, 28 Aug 2013 08:20:12 GMT

The delete statement can be used conditionally.

data want;

set have;

if var5="" /*null value for character variable */

var6=. /*null value for numeric variable */

then delete;

run;

Re: Proc transpose

Murray_Court — Fri, 23 Aug 2013 12:24:07 GMT

Hi, solution below, I saw no need to transpose.

data x1set;

set mydata;

drop x2;

run;

data x2set;

set mydata;

drop x1;

run;

data newdata;

set x1set x2set;

run;

Re: Testing for significant differences of time series

Murray_Court — Fri, 23 Aug 2013 08:23:30 GMT

It looks like Analysis of Variance is your solution here.

The assumptions made about your data must be as follows:

- Observations are independent (has you data been collected properly)

- Errors are normally distributed

- Both groups have equal response variences

You have given a very small sample; the following program tells me through a homogeneity of variences test that the differences in variences between the groups are within standard statistical parameters (only just).

data temp;

input low high;

datalines;

0.1 1.1
0.5 0.4
0.4 1.4
0.6 0.6
0.1 0.2
0.5 0.2
0.6 0.8

;

data temp_1;

team=1;

set temp (keep=low rename=(low=score));

run;

data temp_2;

team=2;

set temp(keep=high rename=(high=score));

run;

data temp;

set temp_1 temp_2;

run;

proc glm data=temp;

class team;

model score=team;

means team / hovtest;

quit;

With such a small sample size it is very hard to say wether the errors are normally distributed.

Having done our best to verify the assumptions (if you have more data you will be able to do this properly, just run a histrogram of the differenced between the mean and the observations for each group and verify that it looks like a bell curve), we can now proceed with an analysis of varience test, which is also included in the output of the GLM procedure of the abocve program.

The differences in varience yeild an F-statistic (strength and consistency of difference between means) of 2.02. The chances of this happening randomly on the assumption that there was no difference between the groups is given to be around 18.1%, this is above the common statistical threshold of 5%, implying that we cannot conclude that the two groups are different based on our observations.

Adding more observations to our data help us to determine with greater accuracy what is really the case here, as generally speaking when sample sizes are below 30 most statistical tests will be inconclusive.

Hope this helps,

-Murray

Re: Aggregating rows

Murray_Court — Thu, 22 Aug 2013 12:17:06 GMT

Ok, then use this code

Proc sql;

create table want as

select _NAME_,

sum(col1) as col1tot,

sum(col2) as col2tot,

sum(col3) as col3tot

from Aero_Final_Trans

group by _NAME_;

quit;

I suspect that it will not group by _NAME_ properly, as you have changed the format to display the month only but not the underlying value. If it gives you far too many monthly totals then you should use the PUT function as opposed to the INPUT one in your initial stage, this will give you a character variable and not just the facade of one.

data Aero_Final_Trans;

set Aero_Final_Trans (rename=(_NAME_= TEMP));

_NAME_= put(TEMP,MONYY5.);

Format _NAME_ MONYY5.;

DROP temp;

run;

Re: Aggregating rows

Murray_Court — Thu, 22 Aug 2013 11:16:51 GMT

Hi,

It looks like you are trying to group by month, when the month name is only the format, and the underlying date values can still be distinct within each month. This will make aggregation difficult.

What do you mean by 'group them by NAME', are you trying to sum COL1 - COL3 and then sum again by Name?

If so then try:

Proc sql;

create table want as

select _NAME_, sum(col1 + col2 + col3) as total

from Aero_Final_Trans

group by _NAME_;

quit;

Re: Data management question

Murray_Court — Thu, 22 Aug 2013 07:36:48 GMT

This should work:

data temp;
input Group ID Var;
datalines;
1     11     111
1     22     222
1     33     333
2     11     111
2     33     333
2     22     222
3     22     222
3     11     111
3     33     333
;

run;

proc sort data=temp(keep=group id) out=temp2;
by group ID;
run;

data want;
set temp2;
set temp(keep=Var);
run;

Re: replace missing value with mean

Murray_Court — Thu, 22 Aug 2013 07:30:47 GMT

Works well with this dataset:

data one;

input thing;

datalines;

100

101

103

104

105

;

Can you give the log or the dataset you tried to use it with?

Re: Assigning values for postal codes

Murray_Court — Thu, 22 Aug 2013 06:17:58 GMT

Some conditional logic should help here, with the use of the substring function.

I don't have SAS o my home computer so I havnt been able to check for errors.

/* define lists of numbers and letters, although I suspect SAS may have a shortcut for this. */

%let nums = ("1", "2", "3", "4", "5", "6", "7", "8", "9", "0");

%let letters = ("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z");

data want;

set have;

upcase(substr(postal_code,1,1)). = "T"

AND

substr(postal_code,2,1) in &nums.

AND

upcase(substr(postal_code,3,1)) in &letters.

AND

substr(postal_code,4,1) = " "

AND

substr(postal_code,5,1) in &nums.

AND

upcase(substr(postal_code,6,1)) in &letters.

AND

substr(postal_code,7,1) in &nums.

then validation = "correct";

/*if the mength of your field is more than 7 characters you may want to check if the remaining ones are blank,

in your example the field post_code has 7 characters so no check needed */

else if

upcase(substr(postal_code,1,1)) in &letters

AND

substr(postal_code,2,1) in &nums.

AND

upcase(substr(postal_code,3,1)) in &letters.

AND

substr(postal_code,4,1) = " "

AND

substr(postal_code,5,1) in &nums.

AND

upcase(substr(postal_code,6,1)) in &letters.

AND

substr(postal_code,7,1) in &nums.

then validation = "wrong "; /*extra spaces needed to avoid truncation */

else validation="strange";

Re: Adding extra data to existing dataset

Murray_Court — Wed, 21 Aug 2013 14:17:01 GMT

PROC APPEND is your answer.

proc append base=have;

data=extra;

run;

Simply put all of your additional data into a new dataset with the same structures and field names. Use the FORCE option to add observations with new variables .