robm Tracker

Re: eliminating duplicates in a data step

robm — Thu, 19 Oct 2017 15:37:52 GMT

Hi Reza

the original dataset has unique data by the fact that the measure has a $value for classes a student took , I am trying to make a "LIMIT" dataset in the work. area on the fly for this web based stored process so it needs to just get the filter fields in a unique form. But when I use the data step method i have no way of filtering the duopes unles i use the proc sql; or sort ...does that make sense?

Re: proc tab and a difference coulmn

robm — Thu, 19 Oct 2017 13:19:58 GMT

Thanks Ballard

Summary before is the way i went

Re: eliminating duplicates in a data step

robm — Thu, 19 Oct 2017 13:18:11 GMT

yeah I was afraid of that , I currently am using proc sql; with a select distinct to filter them out

Re: eliminating duplicates in a data step

robm — Thu, 19 Oct 2017 13:17:05 GMT

You are right it does

thanks Kurt

eliminating duplicates in a data step

robm — Thu, 19 Oct 2017 06:01:53 GMT

I have a data step like this that creates a 4gb file , unfortunately it has duplicates that would reduce it too 150,000kb is there an equivalent of “distinct” for the sas syntax below.

  data ROBM.LIMITFILE1;
    set _egimle.&timeperiodvalue (keep=&limitvar1field &limitvar1fielddesc &limitvar2field &limitvar2fielddesc &limitvar3field &limitvar3fielddesc &limitvar4field
                                        &limitvar4fielddesc &limitvar5field &limitvar5fielddesc &limitvar6field &limitvar6fielddesc &limitvar7field &limitvar7fielddesc
                                        &limitvar8field &limitvar8fielddesc filter=("&trendvalue" AND "&timeperiodfilter"));

proc tab and a difference coulmn

robm — Tue, 10 Oct 2017 22:13:40 GMT

I have a proc tab

option validvarname=any;
libname stdnt "E:\SAS\Data\Student";
libname robm "f:\robm";

proc tabulate data=ROBM.dump missing contents = ' ' format=COMMA6.0 S=[foreground=highlight.];
   class 'Reporting Year'n 'Responsible College'n 'Reporting Year'n;
   var 'Final Charge'n;
   keylabel Sum = " ";
   table  ('Responsible College'n ="")  all=Total , (('Reporting Year'n="Reporting Year" )*(('Final Charge'n = "")*F=COMMA12.0)) /
                  contents = ' ' misstext=' ' box={label="test"};
  run;

that gives

How do I get a 4^th column that is the difference of 2017/2018 – 2016/2017 values?

list all srx reports in a sas 9.4 ebi install

robm — Mon, 11 Sep 2017 19:43:50 GMT

How would I list all the SRX reports using SAS cmds or a sql query in pgAdmin

Re: Subtotal on a proc tabulate

robm — Mon, 26 Jun 2017 17:51:49 GMT

aaagh dammit you are correct the other one is my "company account" that we have paid support for

Subtotal on a proc tabulate

robm — Sun, 25 Jun 2017 23:41:00 GMT

I have a macro with 3 proc tab statements one with 1 row param one with 2 row params and one with 3 row params

what i want on all of then is for there to be a row subtotal after the &pr param

I tried

class &pr*(Total) &sr &tr &col; but that didnt work ...any suggestions

%macro tab;
%if &sr = niu %then %do;
proc tabulate data=WORK.TBIR11052_CACHED missing contents = ' ' &measurefmt S=[foreground=highlight.];
class &pr &col;
var &mes;
keylabel Sum = " ";
table (&pr = ' ') all='Total', ((&col = ' ' all = 'Total')*((&measureexp)))/
contents = ' ' misstext=' ' box={label="&pr" };
%end;
%else %if &tr = niu %then %do;
proc tabulate data=WORK.TBIR11052_CACHED missing contents = ' ' &measurefmt S=[foreground=highlight.];
class &pr &sr &col;
var &mes;
keylabel Sum = " ";
table (&pr = ' ')*(&sr = ' ' ) all='Total', ((&col = ' ' all = 'Total')*((&measureexp)))/
contents = ' ' misstext=' ' box={label="&pr / &sr" };
%end;
%else %if &tr ^= niu and &sr ^= niu %then %do;
proc tabulate data=WORK.TBIR11052_CACHED missing contents = ' ' &measurefmt S=[foreground=highlight.];
class &pr &sr &tr &col;
var &mes;
keylabel Sum = " ";
table (&pr = ' ')*(&sr = ' ' )*(&tr = ' ' ) all='Total', ((&col = ' ' all = 'Total')*((&measureexp)))/
contents = ' ' misstext=' ' box={label="&pr / &sr / &tr" };
%end;
%MEND TAB;
%tab;

Re: sas in web based storedProcess paramater handeling

robm — Tue, 16 May 2017 20:20:39 GMT

this seems to have fixed my problem thanks Quentin

%macro symgetvariables;
ccc=symget('ccc');
tc=symget('tc'); /*term code*/
tca=symget('tca'); /*term code*/
ay=put(symget('ay'),$4.);
ib=symget('ib');
sb=symget('ib');
mes=symget('mes');
pr=symget('pr');
sr=symget('sr');
tr=symget('tr');
col=symget('col');
mc=symget('mc');
smc=symget('smc');
smd=symget('smd');
sl=symget('sl');
cl=symget('cl');
ac=symget('ac');
sm=symget('sm');

%if %sysevalf(%superq(ib)=,boolean) %then %let ib=%;
%if %sysevalf(%superq(sb)=,boolean) %then %let sb=%;
%if %sysevalf(%superq(sl)=,boolean) %then %let sl=%;
%if %sysevalf(%superq(smd)=,boolean) %then %let smd=%;
%if %sysevalf(%superq(sm)=,boolean) %then %let sm=%;
%if %sysevalf(%superq(smc)=,boolean) %then %let smc=%;
%if %sysevalf(%superq(ac)=,boolean) %then %let ac=%;
%if %sysevalf(%superq(ccc)=,boolean) %then %let ccc=%;
%if %sysevalf(%superq(cs)=,boolean) %then %let cs=%;
%if %sysevalf(%superq(cl)=,boolean) %then %let cl=%;

%mend;

Re: sas in web based storedProcess paramater handeling

robm — Tue, 16 May 2017 17:44:34 GMT

I tried this and sm still isnt set to %

%let SM=;
%macro symgetvariables;
ccc=symget('ccc');
tc=symget('tc'); /*term code*/
tca=symget('tca'); /*term code*/
ay=put(symget('ay'),$4.);
ib=symget('ib');
sb=symget('ib');
mes=symget('mes');
pr=symget('pr');
sr=symget('sr');
tr=symget('tr');
col=symget('col');
mc=symget('mc');
smc=symget('smc');
smd=symget('smd');
sm=symget('sm');
sl=symget('sl');
cl=symget('cl');
ac=symget('ac');
%mend;

data _null_;
%symgetvariables;

if sm=" then sm='%';
put sm=;

run;

Re: sas in web based storedProcess paramater handeling

robm — Tue, 16 May 2017 17:18:00 GMT

ok yes Id rather use macro If statments if that allows me to do this in the %marco symgetvariables ...how would I do that

sas in web based storedProcess paramater handeling

robm — Tue, 16 May 2017 16:50:37 GMT

I have a SP that gets parameters from the url like this

what i want to happen is if a parameter is empty like

t&SMC=&ccc=&ac=&cs=&cl=&smd=&sm=&sl

like sm above have a wildcard inserted like this:

if SM= then SM=%;

so that this data step where filter will work

where ("STUDENT MAJOR CODE"n LIKE "&SM") however i get the error , any ideas?

NOTE: Line generated by the invoked macro "SYMGETVARIABLES".
65         ('mc');    smc=symget('smc');    smd=symget('smd');    sm=symget('sm');    sl=symget('sl');    cl=symget('cl');    ac=symget('ac');        if SM="" then SM=%;
                                                                                                                                                                       _
                                                                                                                                                                       386
                                                                                                                                                                       200
ERROR 386-185: Expecting an arithmetic expression.

ERROR 200-322: The symbol is not recognized and will be ignored.

Re: Need step for SAS Logon Manager and CAS authentication.

robm — Wed, 10 May 2017 19:15:41 GMT

Sure

I have it all working perfectly , I have a document as well as the windows compiled apache mod (that was the biggest P.I.T.A.)

as well as instructions on how to build the mod itself if need be what email address would you like it sent too.

I am hoping to write a white paper on it or do a global forum talk if they still do E.B.I. at global forum.

Re: using proc sql outobs=1 and selecting into a variable how do i do that?

robm — Fri, 21 Apr 2017 05:29:43 GMT

that works great thanks

using proc sql outobs=1 and selecting into a variable how do i do that?

robm — Thu, 20 Apr 2017 21:39:47 GMT

if I have 1 observation from a proc sql query how do i assign that to a variable? that i want to load into a variable

proc sql outobs=1;
SELECT "Qa Test Id"n FROM ROBM.QUAL102;
run

so that further on I can do the below sort if the &var (QA Test ID) from the select above is 35 for instance

if &var = 35 then do;
 proc sort data=ROBM.QUAL102;
  by TERM_CODE "Id"n CRN;
quit;
end;

Re: Proc Tabulate and summing measures

robm — Tue, 18 Apr 2017 13:20:32 GMT

yup sorry I should have spent more time examining and understanding my PROC TABULATE

Re: Proc Tabulate and summing measures

robm — Tue, 18 Apr 2017 02:19:56 GMT

you aew right I building these of a EBI server

  proc tabulate data=ROBM.TABBS99991 missing contents = ' ' format=COMMA6.0 S=[foreground=highlight.];
   class &showvarvalue &showvarvalue1 &acrossvarvalue;
   var &measurevar;
   keylabel Sum = " ";
   table &showby1exp &showby2exp &showvarall, (&acrossexp(&measureexp)) /
                  contents = ' ' misstext=' ' box={label="&boxlabel"};
  run;

and (&acrossexp(&measureexp)) should have just been (&measurevar)

  proc tabulate data=ROBM.TABBS99991 missing contents = ' ' format=COMMA6.0 S=[foreground=highlight.];
   class &showvarvalue &showvarvalue1 &acrossvarvalue;
   var &measurevar;
   keylabel Sum = " ";
   table &showby1exp &showby2exp &showvarall, (&measurevar) /
                  contents = ' ' misstext=' ' box={label="&boxlabel"};
  run;

  proc tabulate data=ROBM.TBIR1025 missing contents = ' ' format=COMMA6.0 S=[foreground=highlight.];
   class 'ALLOCATED_COLLEGE'n 'SUBJECT_DESC'n 'TERM_CODE'n;
   var STUDENT_COUNT;
   keylabel Sum = " ";
   table ('ALLOCATED_COLLEGE'n =' ') *('SUBJECT_DESC'n=' ' all='Total') all='Total', (('TERM_CODE'n=' ' all='Total')*((STUDENT_COUNT))) /
                  contents = ' ' misstext=' ' box={label="ALLOCATED_COLLEGE/SUBJECT_DESC"};
  run;


  proc tabulate data=ROBM.TBIR1025 missing contents = ' ' format=COMMA6.0 S=[foreground=highlight.];
   class 'ALLOCATED_COLLEGE'n 'SUBJECT_DESC'n 'TERM_CODE'n;
   var INCLUDED_TUITION;
   keylabel Sum = " ";
  table (ALLOCATED_COLLEGE = ' ') *(SUBJECT_DESC = ' ' all = 'Total') all='Total', ((TERM_CODE = ' ' all = 'Total')*((INCLUDED_TUITION))) /
                  contents = ' ' misstext=' ' box={label = "ALLOCATED_COLLEGE/SUBJECT_DESC"};
  run;

Proc Tabulate and summing measures

robm — Mon, 17 Apr 2017 23:08:34 GMT

I have 2 Proc tabulates where I am trying to show 2 different measure summed over different acacdemic year terms basically i am trying to sum in one

-the Tuition from each ALLOCATED_COLLEGE/SUBJECT_DESC for each column TERM_CODE

-the Student count for each ALLOCATED_COLLEGE/SUBJECT_DESC for each column TERM_CODE

IE I have change the VAR for each but they still sum exactly the same ...any suggestions?

the output from below can be seen in the attached pdf

proc tabulate data=ROBM.TBIR1025 missing contents = ' ' format=COMMA6.0 S=[foreground=highlight.];
class ALLOCATED_COLLEGE SUBJECT_DESC TERM_CODE;
var INCLUDED_TUITION;
keylabel Sum = " ";
table (ALLOCATED_COLLEGE =' ') *(SUBJECT_DESC=' ' all='Total') all='Total', ((TERM_CODE=' ' all='Total')*((N=Amount))) /
contents = ' ' misstext=' ' box={label="ALLOCATED_COLLEGE/SUBJECT_DESC"};
run;
 
proc tabulate data=ROBM.TBIR1025 missing contents = ' ' format=COMMA6.0 S=[foreground=highlight.];
class ALLOCATED_COLLEGE 'SUBJECT_DESC'n 'TERM_CODE'n;
var STUDENT_COUNT;
keylabel Sum = " ";
table (ALLOCATED_COLLEGE =' ') *(SUBJECT_DESC=' ' all='Total') all='Total', ((TERM_CODE=' ' all='Total')*((N=Count))) /
contents = ' ' misstext=' ' box={label="ALLOCATED_COLLEGE/SUBJECT_DESC"};

Re: proc tabulate and quoted string error

robm — Mon, 17 Apr 2017 22:57:24 GMT

got it that makes sense now thanks all