CTorres Tracker

Re: Time Conversion

CTorres — Wed, 24 Jun 2015 18:08:49 GMT

This is Mark Johnson' solution improved:

data want;

set have;

txt=put(number,z4.);

time = put(input(substr(txt,1,2),2.)*3600+(input(substr(txt,3),2.)*60),timeampm.);

drop txt;

run;

CTorres

Re: re: email attachments

CTorres — Mon, 22 Jun 2015 18:47:55 GMT

Well, I am not very good using macro language specially when I have to deal with quotes and special characters like in this case.

If I were you I would avoid using macros because in my opinion SAS Base language is a much better tool to do what you need. I would:

1. Run SAS code (without macros) to test the email with one attachment.

2. Run SAS code to test the email with two or more attachments.

3. Once I am sure about the syntax then I would use the SAS Data Step to generate the list of n files using SAS Base (no macro at all).

I believe this is a safer approach than using macros.

Good luck

CTorres

Re: Import zip csv file to SAS, Date variable values missing

CTorres — Mon, 22 Jun 2015 16:38:36 GMT

Try reading Sale_date using an informat:

input Sale_date MMDDYY10. City_CD Brand $ MODEL_YEAR Units;

CTorres

Re: re: email attachments

CTorres — Mon, 22 Jun 2015 16:27:24 GMT

I think the problem here is that you can have only one ATTACH= option in the FILE statement. So you have to use the loop to generate the list of files to attach and the use only one attach= option. Something like: (untested and may be more complex than that)

FILENAME mail EMAIL;

DATA _NULL_;

FILE mail

TO = "Lisa1245@yahoo.com"

FROM = "argo34@yahoo.com"

%macro send_email;

%LET mylist=;

%DO i=1 %TO %SYSFUNC(COUNTW(&FILENAMELIST1,’|’));

%LET NEXT_FILENAMELIST1 = %SCAN(&FILENAMELIST1,&i,|);

&LET mylist=&mylist. "%sysfunc(pathname(project))\&NEXT_FILE NAMELIST1..PDF"; /* here you build the list */

%END;

ATTACH=("&mylist");

PUT "Hi Lisa,";

PUT " ";

PUT "This is test";

PUT " ";

PUT "Thanks,"

RUN;

QUIT;

%mend;

CTorres

Re: insert rows based on output from subtracting two rows in the same column

CTorres — Wed, 17 Jun 2015 15:52:55 GMT

You can recreate the email sequence with the following additional Data step:

data want(rename=seq=email);

set want(drop=email);

by ID;

retain seq;

if first.id then seq=1;

else seq=seq+1;

run;

CTorres

Re: Read Hierarchical Files in SAS DI Studio

CTorres — Wed, 17 Jun 2015 14:43:44 GMT

As far as I understand there isn't an out-the-box transformation to do that but you can use either the User Written Code Transformation or the New Transformation Wizard to generate the required SAS code.

CTorres

Re: Macro to Combine Municipal Proc SQL Statements Based on Date Criteria

CTorres — Fri, 12 Jun 2015 19:27:11 GMT

Proc SQL produces ONLY one output Dataset.

You can use a SAS Data Step to generate many datasets in one pass.

The following program creates the three datasets you want using the today as the reference date to count the 0-12, 13-24 and 25-36 months back.

data Customers_Active Customers_Inactive Customers_Lapsed;

set transaction_vw;

if transaction_date > today()-365 then output Customers_Active;

else if transaction_date > today()-730 then output Customers_Inactive;

else if transaction_date > today()-1095 then output Customers_Lapsed;

run;

Use the program as a starting point to get your results.

Hope this helps,

CTorres

Re: Creating Last First Mi from a single variable

CTorres — Thu, 11 Jun 2015 20:01:35 GMT

Very simplistic but works if there are no composed names, prefixes, more than one initial, etc...:

data have;

name='Doe, John M';

last=scan(name,1);

first=scan(name,2);

Mi=scan(name,3);

run;

CTorres

Re: Removing Character Value from String at Specific Position in String

CTorres — Thu, 11 Jun 2015 14:40:39 GMT

Yes, It works OK but I would improve the program with a compress at the end to remove the blanks:

data want;

set have;

want=have;

substr(want,ifn(position-1=0,1,position-1)*2,2)='';

want=compress(want);

run;

CTorres

Re: Removing Character Value from String at Specific Position in String

CTorres — Wed, 10 Jun 2015 20:08:43 GMT

My program works as you want:

data have ;

length ID position 8 Have $ 20;

input ID position have;

cards;

1000 2 N,N,Y

1001 2 Y,N,N,N,Y

1002 4 N,N,N,N,N

1003 6 N,Y,Y,N,Y,Y,N

1004 5 N,Y,N,N,N,Y,N

1005 3 Y,N,Y

;

RUN;

data want;

set have;

length Wanted $20;

do i=1 to countw(have);

if i=position then continue;

wanted=cats(wanted,scan(have,i),',');

end;

wanted=substr(wanted,1,length(wanted)-1);

drop i;

run;

CTorres

Re: Removing Character Value from String at Specific Position in String

CTorres — Wed, 10 Jun 2015 19:13:38 GMT

When you say the position in the string is dynamic I assume that this value is known in every observation.

The following program removes the text found in different positions (del variable) in the text separated by commas:

data have ;

length del 8 text $ 20;

input del text;

cards;

2 N,N,Y,Y,N,XX,G

4 YX,N,Y,N,N,Y,AA

1 N,X,T,Y,Y,A,F

3 Y,XX,N,YY,DF

3 M,N,N,Y,YY,XX,Z

8 Y,N,Y,Y,Y,Y,Y,D

;

Run;

data want;

set have;

length newtext $20;

do i=1 to countw(text);

if i=del then continue;

newtext=cats(newtext,scan(text,i),',');

end;

newtext=substr(newtext,1,length(newtext)-1);

drop i;

run;

I hope this is what you want,

CTorres

Re: create .csv without blanks

CTorres — Tue, 09 Jun 2015 18:55:56 GMT

IMO the blank will not hurt at all when you read the .cvs file.

Take into account that SAS represents missing values with a . for numeric variables and with a blank for character variables.

CTorres

Re: Proc Means to SQL code

CTorres — Wed, 13 May 2015 20:59:23 GMT

So, the following would be the solution (untested) for your proc means assuming the class variables are character type:

proc sql noprint;

create view type11 as

select distinct source, Year, /* from by statement in Proc means */

cltname, incentive, /* from class statement in Proc means */

'11' as _TYPE_, count(*) as _FREQ_, /* _TYPE_ and _FREQ_ variables */

Sum(Elig) as ELIG, sum(overall) as overal, /* SUM statistics for var statement */

Sum(initial) as initial, sum(consecutive) as conservative

from temp

group by source, Year, cltname, incentive; /* from both by and class statements */

create view type10 as

select distinct source, Year,

cltname, ' ' as incentive,

'10' as _TYPE_, count(*) as _FREQ_,

Sum(Elig) as ELIG, um(overall) as overal,

Sum(initial) as initial, sum(consecutive) as conservative

from temp

group by source, Year, cltname;

create view type01 as

select distinct source, Year,

' ' as cltname, incentive,

'01' as _TYPE_, count(*) as _FREQ_,

Sum(Elig) as ELIG, um(overall) as overal,

Sum(initial) as initial, sum(consecutive) as conservative

from temp

group by source, Year, incentive;

create view type00 as

select distinct source, Year,

' ' as cltname, ' ' as incentive,

'00' as _TYPE_, count(*) as _FREQ_,

Sum(Elig) as ELIG, um(overall) as overal,

Sum(initial) as initial, sum(consecutive) as conservative

from temp

group by source, Year;

create table smry as

select * from type11

union all

select * from type10

union all

select * from type01

union all

select * from type00

order by 1,2,3,4;

quit;

CTorres

Re: Proc Means to SQL code

CTorres — Wed, 13 May 2015 20:21:47 GMT

Hi Aanch16167,

The following example shows how to convert a PROC MEANS to PROC SQL. You can use it as starting point to create your own solution:

data grade;

input Name $ 1-8 Gender $ 11 Status $13 Year $ 15-16

Section $ 18 Score 20-21 FinalGrade 23-24;

datalines;

Abbott F 2 97 A 90 87

Branford M 1 98 A 92 97

Crandell M 2 98 B 81 71

Dennison M 1 97 A 85 72

Edgar F 1 98 B 89 80

Faust M 1 97 B 78 73

Greeley F 2 97 A 82 91

Hart F 1 98 B 84 80

Isley M 2 97 A 88 86

Jasper M 1 97 B 91 93

Charly M 1 B 90 90

Sandra F 2 98 95 88

;

run;

proc sort data=Grade out=GradeBySection;

by section;

run;

/****** PROC MEANS *******/

proc means data=GradeBySection missing noprint chartype;

by Section;

var Score FinalGrade;

class Status Year;

output out=Want_Means sum=;

run;

proc sort data=Want_Means;

by Section Status Year _TYPE_;

run;

/******* PROC SQL *******/

proc sql noprint;

create view type11 as

select distinct Section, Status, Year, '11' as _TYPE_,

count(*) as _FREQ_, sum(Score) as Score, sum(FinalGrade) as FinalGrade

from grade

group by Section, Status, Year;

create view type10 as

select distinct Section, Status, ' ' as Year, '10' as _TYPE_,

count(*) as _FREQ_, sum(Score) as Score, sum(FinalGrade) as FinalGrade

from grade

group by Section, Status;

create view type01 as

select distinct Section, ' ' as Status, Year, '01' as _TYPE_,

count(*) as _FREQ_, sum(Score) as Score, sum(FinalGrade) as FinalGrade

from grade

group by Section, year;

create view type00 as

select distinct Section, ' ' as Status, ' ' as Year, '00' as _TYPE_,

count(*) as _FREQ_, sum(Score) as Score, sum(FinalGrade) as FinalGrade

from grade

group by Section;

create table Want_SQL as

select * from type11

union all

select * from type10

union all

select * from type01

union all

select * from type00

order by 1,2,3,4;

quit;

/****** Compare the results *******************/

proc compare data=Want_Means compare=Want_SQL;

run;

I hope this helps,

CTorres

Re: Join tables

CTorres — Thu, 23 Apr 2015 19:48:45 GMT

The same code works OK with the new supposition: if all values of PWPUMAS are the same then MAX(PWPUMAS) is this value for each IN_PUMA_ID

You can also use the following code assuming both files are sorted by IN_PUMA_ID and PWPUMAS has the same value for each IN_PUMA_ID:

Data new;

merge Agearank (in=in_rank) Agea (keep=IN_PUMA_ID PWPUMAS);

by IN_PUMA_ID;

if in_rank;

run;

CTorres

Re: Join tables

CTorres — Thu, 16 Apr 2015 14:44:07 GMT

Try this to attach the value of MAX(PWPUMAS) by each IN_PUMA_ID group:

proc sql noprint;

create table new as

select A.*, MPWPUMAS as PWPUMAS

from Agearank A

left join

(select distinct IN_PUMA_ID, MAX(PWPUMAS) as MPWPUMAS

from Agea B

group by IN_PUMA_ID)

on (A.IN_PUMA_ID = B.IN_PUMA_ID)

order by 1;

quit;

CTorres

Re: Join tables

CTorres — Thu, 16 Apr 2015 14:20:07 GMT

There are many values of the variable PWPUMAS for each IN_PUMA_ID. You need to chose what of them to attach. Maybe MAX(PWPUMAS) ?

Re: problem with dates

CTorres — Wed, 15 Apr 2015 16:29:58 GMT

IMO you should not have to do this "complex" transformation from a text field to another text field if you use SAS Dates with formats:

From text to text (your solution):

data final;

date_hoc="20140202"; /* text field */

run;

proc sql;

create table Report as

select

Put(mdy(input(substr(strip(put(DATE_HOC,$10.)),5,2),2.),input(substr(strip(put(DATE_HOC,$10.)),7),2.),input(substr(strip(put(DATE_HOC,$10.)),1,4),4.)),date9.) as Date_Hoc

from final;

quit;

From numeric field to SAS Date (number) - my suggestion :

data final;

date_hoc=20140202; /* numeric field */

run;

proc sql;

create table Report as

select

input(DATE_HOC,yymmdd8.) as Date_Hoc format date9. /* SAS date */

from final;

quit;

The advantage of a SAS Date is that you can display it in many ways using different formats and also you can get more information from the date using functions as YEAR, MONTH, DAY, etc.

CTorres

Re: Incorrect sum of julian date

CTorres — Tue, 14 Apr 2015 20:48:04 GMT

Try this:

data chk;

informat dat date9.;

format dat julian5. ;

input dat ;

juldate=input(put(dat,julian5.),5.);

cards;

23-Nov-73

26-Nov-73

23-Nov-73

26-Nov-73

;

run;

proc sql;

select sum(juldate) from chk;

quit;

Regards,

Re: Error PATH= is converting into XXXX

CTorres — Tue, 14 Apr 2015 20:38:41 GMT

Please provide the libname statement you are using.

Make sure you are using the engine ORACLE:

LIBNAME oralib ORACLE PATH=path SCHEMA=schema USER=user PASSWORD="password" ;