About Pamela_JSRCC

Pamela_JSRCC · ‎09-21-2016

I have to do something similar at work to report enrollment comparisons for the current term compared to the previous year's term. Given a list of associated dates for the current year with the previous year, I create a table that translates the current date to the "comparative" one for the previous year. The "hard" dates are typically holidays like Labor Day. Otherwise I try to match the same weekday so that weekends match up. Once the table is created, it is easy to compare the appropriate dates. Hope this helps.

Pamela_JSRCC · ‎08-27-2016

How are the values stored? Who/what produces the calculated values? Given a dataset, it is easy to get the min, max ,avg, etc. of the values, so I'm assuming that you need to access the values that SAS produces. Most of the procs that I've used have a way to save an output dataset with the results that you can analyze.

Pamela_JSRCC · ‎08-26-2016

Please provide a sample dataset and desired results.

Pamela_JSRCC · ‎06-10-2016

/* create table with all months between the minimum and maximum months in the data */ data yr_mo; input yr mo; datalines; 2014 12 2015 01 2015 02 . . . 2015 12 ; run; data yr_mo; /* add sequence field */ seq = _n_; set yr_mo; run; proc sql; create table temp as select c.id, b.yr, b.mo from yr_mo A join yr_mo B on b.seq = a.seq +1 /* month after that in A */ join data C on c.yr = a.yr and /* active for month in A */ c.mo = a.mo left join data D on d.id = c.id and d.yr= b.yr and d.mo=b.mo where missing(d.id) /* inactive for month in B */ ; proc sql; select id, count(*) as row_cnt from temp group by id;

Pamela_JSRCC · ‎06-08-2016

proc sql; select a.* from table1 A left join table2 B on b.student_id = a.student_id and b.course = a.course where missing (b.student_id);

Pamela_JSRCC · ‎04-26-2016

You can create an array out of variables already defined, someting like array c1-c20 itinerary_cab_1_s itinerary_cab_2_s ... itinerary_cab_19_s itinerary_cab_20_s; Something like the following datastep can help you generate the variable list: * Write list of variables to the log; data _null; name_list length 1024; name_list = ''; do i=1 to 20; name=cats('itinerary_cab_', put(i,2.), '_s'); name_list = catx(' ', name_list, name); end; put '(' name_list ')'; run; I don't have SAS installed on my tablet, so the above code has not been tested.

Pamela_JSRCC · ‎04-26-2016

Most of the code is yours; thats why I made it so small except for where I didn't. The main idea is that you can create your graphs separately with proc sgPlot and fiddle around 'til you get what you want. Then use the TMPLOUT= option to put the template code into text files. You can then copy the layout statements from the template files into the proc template code that controls the layout. I've attached a PNG of the template files in Notepad showing the part I copied. A picture is worth a 1,000 words. This is my first time using proc template for graphics, so there may be a better way, but this is simpler than I thought it would be.

Pamela_JSRCC · ‎04-26-2016

That should not be a problem. Proc template and proc render are both available in SAS 9.3. You may have to recreate the template files to get the template code generated from your SAS version. That's what I did when running it in SAS 9.3. I've attached a PNG file showing what the graph looks like.

Pamela_JSRCC · ‎04-26-2016

I used proc template to display your graphs in a lattice with 1 column. I ran your proc sgplot statements to create code for the layouts for proc template. Then combined them in a proc template statement below (just copied the layout statement from each of the template files plot_top.tmp and plot_bttm.tmp into the proc template statement than creates the sgplot_top_bttm template. Then proc sgRender is called to render the graphs using the data provided. proc sgplot data=graph_data noautolegend nocycleattrs TMPLOUT= "&xpath.\plot_top.tmp" ; refline 1 / axis=x lineattrs=(thickness=1 color=black); series x=lbdy y=lbstresn / lineattrs =(pattern=solid thickness=3) nomissinggroup name='bp' markerattrs=(symbol=circlefilled size=11); scatter x=lbdy y=lbstresn / markerattrs=(symbol=circlefilled size=11) datalabel=lbstresn; scatter x=lbdtc y=lbstresn / markerattrs=(size=0) x2axis; yaxis display=(nolabel noticks novalues) grid; y2axis display=none ; x2Axis notimesplit tickvalueformat=yymmdd10. label="Study Date" offsetmin=&offsetMin offsetmax=&offsetMax values =(&mindate10 to &maxdate by 10) FITPOLICY=rotate; xAxis grid label='Study Days' offsetmin=&offsetMin offsetmax=&offsetMax values =(&minday10 to &maxday by 10 ) /*FITPOLICY=rotate*/ display=none; run; proc sgplot data=graph_data noautolegend nocycleattrs TMPLOUT= "&xpath.\plot_bttm.tmp" ; refline 1 / axis=x lineattrs=(thickness=1 color=black); series x=lbdy y=lbstresn / lineattrs =(pattern=solid thickness=3) nomissinggroup name='bp' markerattrs=(symbol=circlefilled size=11); scatter x=lbdy y=lbstresn / markerattrs=(symbol=circlefilled size=11) datalabel=lbstresn; scatter x=lbdtc y=lbstresn / markerattrs=(size=0) x2axis; /*inset ("R:"="0-0")/ border position=topright;*/ yaxis display=(NoLabel NoTicks NoValues) grid; y2axis display=none ; x2Axis notimesplit tickvalueformat=yymmdd10. offsetmin=&offsetMin offsetmax=&offsetMax values =(&mindate10 to &maxdate by 10) FITPOLICY=rotate display=none; xAxis grid label='Relative Study Days' offsetmin=&offsetMin offsetmax=&offsetMax values =(&minday10 to &maxday by 10); run; Ods Graphics Off; *Ods Rtf Close; Ods Listing; /* Get Layout statements from the template files generated above */ proc template; define statgraph sgplot_top_bttm; begingraph / collation=binary; layout lattice / columns=1 rowDataRange=union; /* layout statement copied from plot_top.tmp */ layout overlay / xaxisopts=( display=none Label="Study Days" labelFitPolicy=Split offsetmin=0.02 offsetmax=0.03 type=linear linearopts=( tickvaluelist=( 1 11 21 31 41 51 61 71 81 91 101 111 121 131 141 151 161 171 181 191 201 211 221 231 241 251 261 271 281 291 301 311 321 331 341 351 361 371 381 391 ) viewmin=1 viewmax=391 ) griddisplay=on ) yaxisopts=( display=( line ) type=linear griddisplay=on ) x2axisopts=( Label="Study Date" labelFitPolicy=Split offsetmin=0.02 offsetmax=0.03 type=linear linearopts=( tickvaluelist=( 20089 20099 20109 20119 20129 20139 20149 20159 20169 20179 20189 20199 20209 20219 20229 20239 20249 20259 20269 20279 20289 20299 20309 20319 20329 20339 20349 20359 20369 20379 20389 20399 20409 20419 20429 20439 20449 20459 20469 20479 ) viewmin=20089 viewmax=20479 TickValueFitPolicy=Rotate TickValueFormat=YYMMDD10. ) ) y2axisopts=( display=none type=auto ); ReferenceLine x=1 / clip=true Lineattrs=( Color=CX000000 Thickness=1); SeriesPlot X='lbdy'n Y='lbstresn'n / includemissinggroup=false Markerattrs=( Symbol=CIRCLEFILLED Size=11) Lineattrs=( Pattern=1 Thickness=3) LegendLabel="lbstresn" NAME="bp"; ScatterPlot X='lbdy'n Y='lbstresn'n / subpixel=off primary=true Markerattrs=( Symbol=CIRCLEFILLED Size=11) LegendLabel="lbstresn" DataLabel='lbstresn'n NAME="SCATTER"; ScatterPlot X='lbdtc'n Y='lbstresn'n / subpixel=off xaxis=x2 Markerattrs=( Size=0) LegendLabel="lbstresn" NAME="SCATTER1"; endlayout; /* layout statement copied from plot_bttm.tmp */ layout overlay / xaxisopts=( Label="Relative Study Days" labelFitPolicy=Split offsetmin=0.02 offsetmax=0.03 type=linear linearopts=( tickvaluelist=( 1 11 21 31 41 51 61 71 81 91 101 111 121 131 141 151 161 171 181 191 201 211 221 231 241 251 261 271 281 291 301 311 321 331 341 351 361 371 381 391 ) viewmin=1 viewmax=391 ) griddisplay=on ) yaxisopts=( display=( line ) type=linear griddisplay=on ) x2axisopts=( display=none labelFitPolicy=Split offsetmin=0.02 offsetmax=0.03 type=linear linearopts=( tickvaluelist=( 20089 20099 20109 20119 20129 20139 20149 20159 20169 20179 20189 20199 20209 20219 20229 20239 20249 20259 20269 20279 20289 20299 20309 20319 20329 20339 20349 20359 20369 20379 20389 20399 20409 20419 20429 20439 20449 20459 20469 20479 ) viewmin=20089 viewmax=20479 TickValueFitPolicy=Rotate TickValueFormat=YYMMDD10. ) ) y2axisopts=( display=none type=auto ); ReferenceLine x=1 / clip=true Lineattrs=( Color=CX000000 Thickness=1); SeriesPlot X='lbdy'n Y='lbstresn'n / includemissinggroup=false Markerattrs=( Symbol=CIRCLEFILLED Size=11) Lineattrs=( Pattern=1 Thickness=3) LegendLabel="lbstresn" NAME="bp"; ScatterPlot X='lbdy'n Y='lbstresn'n / subpixel=off primary=true Markerattrs=( Symbol=CIRCLEFILLED Size=11) LegendLabel="lbstresn" DataLabel='lbstresn'n NAME="SCATTER"; ScatterPlot X='lbdtc'n Y='lbstresn'n / subpixel=off xaxis=x2 Markerattrs=( Size=0) LegendLabel="lbstresn" NAME="SCATTER1"; endlayout; /* end copied lines */ endlayout; /* lattice */ endgraph; end; run; Ods Graphics On / Width=11in Height=7in maxlegendarea=80; Ods Listing close; proc sgrender data = graph_data template = sgplot_top_bttm; run; Ods Graphics Off; Ods Listing;

Pamela_JSRCC · ‎04-26-2016

I was able to run your code and see the problem. To have SAS line up the two graphs, I think you will need to use something that associates the graphs together, like proc sgPanel or proc sgScatter. I don't know if they will give you the flexibility that comes from creating two different x-axes. Hopefully someone with more experience with graphics can suggest a more complete solution.

Pamela_JSRCC · ‎04-25-2016

Not sure if I understand what all your variables mean, so I'm "creating" admit_date to stand in for what you said you wanted from the tables. Also, I'm assuming that patients can be admitted more than once in a year. proc sql; create table CRG.new as select a.patientid, a.sex, a.birthdate, a.sponssn, min(a.admit_date) as first_admit_date, max(e.admit_date) as last_admit_date, from CRG.fy10_crg2 as a, CRG.fy11_crg2 as b, CRG.fy12_crg2 as c, CRG.fy13_crg2 as d, CRG.fy14_crg2 as e where a.patientid=b.patientid=c.patientid=d.patientid=e.patientid group by a.patientID, a.sex, a.birthDate, a.SponSSN; quit; Also, another syntax for the join: proc sql; create table CRG.new as select a.patientid, a.sex, a.birthdate, a.sponssn, min(a.admit_date) as first_admit_date, max(e.admit_date) as last_admit_date, from CRG.fy10_crg2 as a join CRG.fy11_crg2 as b on b.patientID = a.patientID join CRG.fy12_crg2 as c on c.patientID = a.patientID join CRG.fy13_crg2 as d on d.patientID = a.patientID join CRG.fy14_crg2 as e on e.patientID = a.patientID group by a.patientID, a.sex, a.birthDate, a.SponSSN; quit;

Pamela_JSRCC · ‎04-23-2016

When programming, you have to use the same names for variables/columns that SAS recognizes. In you code, you referred to the same value as "date" and "effective_date" and used the phrase "cusip number" instead of "cusip". SAS should have given you grief in the log. Did you look at the log? SAS will tell you what problems it has with your code. Though, keep in mind that that can sometimes be a symptom, not the main cause, of the problem.

Pamela_JSRCC · ‎04-23-2016

Having looked at you dataset (just called "data" below), I think you might want to count the number of firms/cusip by year. If so, you need to get the year from the effect_date and group by that value named "eff_year" below. proc sql; select year(effect_date) as eff_year, count(distinct cusip) as cusip_cnt from data group by eff_year;

Pamela_JSRCC · ‎04-23-2016

You can read multiple files using wildcrds. The code below is not tested but should help get you started. %let data_filepath = c:\...\cust*; * modify path as appropriate for your system; data temp; infile firstobs=2 "&data_filepath"; input product $ customer $ qtr1-qtr4; run;

Pamela_JSRCC · ‎04-23-2016

How are you trying to open the dataset? Do you have a libname statement? Can you post your code and log output?

Online Status	Offline
Date Last Visited	‎06-10-2019 06:31 PM

Re: Is it possible in VA: parallel period?

Re: Minimum and maximum value of calculated numeric variable

Re: SAS VA: How to compare data for all date to data from the same day...

Re: Sequence

Re: Missing Variables within By Group

Re: array and trim

Re: Two x axis allignment

Re: Two x axis allignment

Re: Two x axis allignment

Re: Two x axis allignment

Re: Unable to open a SAS dataset from a library

Re: finding number of firm by cusip and date

Re: "Moving Distinct" Counters

Re: How can I read multiple files placed in a location to one file

Re: Missing Variables within By Group

Re: Two x axis allignment

Re: finding number of firm by cusip and date

Re: Working with text file with blank rows between fields

Re: Is it possible in VA: parallel period?

Re: Minimum and maximum value of calculated numeric variable

Re: SAS VA: How to compare data for all date to data from the same day...

Re: Sequence

Re: Missing Variables within By Group

Re: array and trim

Re: Two x axis allignment

Re: Two x axis allignment

Re: Two x axis allignment

Re: Two x axis allignment

Re: sql to find patients in all fiscal years

Re: finding number of firm by cusip and date

Re: finding number of firm by cusip and date

Re: How can I read multiple files placed in a location to one file

Re: Unable to open a SAS dataset from a library