Hi
The variable name Valid_from_dt seems to indicate, that the "valid from" variable is a datetime value.
As @Kurt_Bremser mentioned please post the output of the Proc Contents data=UMAppealLog; as well as some values.
If valid_from_dt is actually a datetime value, you can use the DATEPART() function to extract the date and use a date constant like "19apr2017"d in your where statement.
Bruno
... View more
Hi
A few things to consider:
what is the type for variable valid_from_date, char or numeric (SAS date)
having dates represented as char value is not recommend
character comparison are always case sensitive, maybe that is why no rows are returned
Sending the log will help in figuring out what is wrong.
Bruno
... View more
Hi
Another way would be, to specify the default length for the format. See sample below.
proc format library=work;
value $cmfreq (default=100)
"1" = "BID"
"2" = "TID"
"3" = "QID"
;
run;
data cm;
infile datalines dlm="¬" truncover;
input cmfreqtot $100.;
cmfreq=put(cmfreqtot, $CMFREQ.);
datalines;
3
2
1
A really long string which should push the twenty character bounds
abc
;
Bruno
... View more
Hi
I would take the free e-Learning SAS Programming 1: Essentials so you can learn all the basics about SAS programming. Lesson 10 will give information about different ways to combine tables.
Bruno
... View more
Right after the %STPBEGIN;
But you should check your code for any Proc that creates output.
Since you call the Stored Process from EG you have easy access to the SAS log.
This will always help
Bruno
... View more
Hi
the question "How is it called?" referred to, how do you run the Stored Process: from SAS Enterprise Guide, from SAS Add-In for Microsoft Office, from the SAS Stored Process web application, etc.
Have you tried the ODS SELECT NONE; statement?
Bruno
... View more
Hi
SAS does have the HHMMSS. informat which can read either hh:mm:ss or hhmmss time values, together with the YYMMDD. informat you can convert the char values to SAS date and time values, the DHMS will make a datetime value out of it.
data have;
input SRTDT $ PSTTM $ STPDT $ PEDTM $;
psttm2 = input(psttm, hhmmss4.);
srtdt2 = input(srtdt, yymmdd8.);
startDate = dhms(srtdt2,0,0,psttm2);
format
psttm2 time8.
srtdt2 date9.
startDate datetime19.
;
datalines;
20150211 1942 20150212 0030
20130826 1220 20130826 1535
;
Bruno
... View more
Hi
Some questions:
What does the Stored Process do, just create tables?
How is it called?
Do you want to inform the caller about how the program run?
As long as your code does not create any output, there will be none.
Otherwise add
ods select none;
statement
Bruno
... View more
Hi
I think it is best to contact technical support for this. While you say it runs under some circumstances but not others, one has to figure out the differences between the two, like SAS version for client and server, session encoding for client and server, etc.
You can run the following on the client as well as on the server to get information on version and session encoding.
%put NOTE: &=sysvlong &=sysscpl;
proc options group=languagecontrol;
run;
Bruno
... View more
Hi
Have a look here https://communities.sas.com/t5/SAS-Stored-Processes/ODS-EXCEL-in-Web-StoredProcess/td-p/286083
Basicaly you will use the follwoing code:
data _null_;
rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
rc = stpsrv_header('Content-disposition','attachment; filename=try3.xlsx');
run;
ods excel file = _webout style=Plateau;
proc print data=sashelp.cars;
run;
ods excel close;
make sure you do not have the %stpbegin and %stpend somewhere in your code.
Bruno
... View more
Hi You could use Proc SQL and find the maximum value for the date within a group, like so:
data have;
infile cards;
input
AccountNumber1 PNumber SNumber Date : mmddyy.
;
format date date9.;
cards;
112335 2233 124 01/01/2017
112335 2233 124 06/01/2017
112335 1111 126 06/01/2017
112335 1111 126 07/01/2017
112335 2233 124 07/01/2017
112257 2234 124 01/01/2017
;
proc sql;
select
AccountNumber1
, PNumber
, SNumber
, max(Date) as lastDate format=date9.
, count(*) as nRows
from
have
group by
AccountNumber1
, PNumber
, SNumber
;
quit;
Bruno
... View more
Hi
Please have a look here http://support.sas.com/documentation/cdl/en/connref/69581/HTML/default/viewer.htm#n1smv71b6303yen17kx46uxc0nxh.htm which gives some explanation on the error you get.
If possible use the same encoding for the client and server sessions.
Bruno
... View more
Hi
You can still use the technique I described with Proc REPORT, just ensure to only select detail rows which also appear in the final structure, then use a left join to combine the final structure with the result created by Proc REPORT, for the final structure data set you should add some seqnr to the rows, so that you can keep the sorting order of the final structure.
Bruno
... View more
Hi
Extending on @PeterClemmensen example you can combine the inA and inB variables so that you know the join type and do whatever you need.
data a;
input id name$;
datalines;
1 x
2 y
3 z
5 a
;
data b;
input id sal;
datalines;
2 100
4 400
5 500
;
proc sort data = a; by id; run;
proc sort data = b; by id; run;
/* Get non matches (id = 2 is the only match)*/
data fulljoin;
merge a (in=ina) b (in=inb);
by id;
length joinType $ 2;
joinType = cats(ina, inb);
run;
You can also use SQL to create the join type varibale:
proc sql;
create table fullJoin_SQL as
select
coalesce(a.id, b.id) as id
, name
, sal
, cats( missing(a.id) = 0, missing(b.id) = 0) as joinType length=2
from
a
full outer join
b
on a.id = b.id
;
quit;
Bruno
... View more
Hi
I assume the number of detail lines in the final datasets may also vary from country to country. I f you want to have the total line before the detail lines, you can use Proc REPORT to calculate this for you. See example below how it could be done.
/* prepare some fake data */
Data a;
Input name $ amount;
name2 = substr(name, 1, anydigit(name) - 1);
Datalines;
Canada1 12.30
Canada2 45.90
France1 35.50
France2 55.50
India1 .
;
/*
* calculate the summary line before the detail lines
* write ou the report data to a SAS dta set
*/
/*ods select all;*/
proc report data=a out=want ;
column name2 name amount;
define name2 / order;
define name / order;
define amount / analysis sum format=comma14.2;
break before name2 / summarize;
run;
/* prepare the data to what we need */
data want2;
length name $ 32;
set want;
if lowcase(_break_) = "name2" then do;
name = catx("_", "Total", name2);
end;
keep name amount;
run;
proc print data=want2;
run;
Bruno
... View more
