VVALUE and VVALUEX functions

VVALUEX

VVALUE

Make contents in legend in ASCENDING order

Include the GROUPORDER=ASCENDING option in the VBARPARM statements.  For example:

vbarparm category=subject response=&var / 
      group=&group datalabel=&byvar dataskin=pressed datalabelattrs=(size=6 weight=bold)
     groupdisplay=cluster clusterwidth=1 grouporder=ascending;

or

keylegend / location=inside position=topright title="Highest Grade" sortorder=ascending;

INSET in Proc SGPLOT

INSET "Mean of the best percent change = &pmean." / position = bottomleft;

Example: Click Here and Here

Import password protected EXCEL into SAS

Click Here

%macro readpass(xlsfile1,xlsfile2,passwd,outfile,sheetname,getnames);

options macrogen symbolgen mprint nocaps; options noxwait noxsync;

%* we start excel here using this routine here   *;

filename cmds dde 'excel|system';

data _null_;
  length fid rc start stop time 8;
  fid=fopen('cmds','s');
  if (fid le 0) then do;
    rc=system('start excel');
    start=datetime();
    stop=start+20;
    do while (fid le 0);
      fid=fopen('sas2xl','s');
      time=datetime();
      if (time ge stop) then fid=1;
      end;
    end;
  rc=fclose(fid);
run; quit;

%* then we open the excel sheet here with its password *;

filename cmds dde 'excel|system';

data _null_;
  file cmds;
  put '[open("'"&xlsfile1"'",,,,"'"&passwd"'")]';
run;

%* then we save it without the password *;

data _null_;
  file cmds;
  put '[error("false")]';
  put '[save.as("'"&xlsfile2"'",51,"")]';
  put '[quit]';
run;

%* Then we import the file here *;

proc import datafile="&xlsfile2" out=&outfile dbms=xlsx replace;
  %* sheet="%superq(datafilm&i)";
  sheet="&sheetname";
  getnames=&getnames;
run; quit;

%* then we destroy the non password excel file here *;

systask command "del ""&xlsfile2"" ";

proc contents data=&outfile varnum;
run;

%mend readpass;

%readpass(j:\access\accpcff\excelfiles\passpro.xlsx, /* name of the xlsx 2007 file */
          c:\sastest\nopass.xlsx,  /* temporary xls file for translation for import */
    mypass,               /* password of the excel spreadsheet          */
           work.temp1,  /* name of the sas dataset you want to write */
           sheet1,     /* name of the sheet */
           yes) ;     /* getnames  */

PROC SGPLOT: Scatter with mean value

Click Here

Fix for invalid characters in data

For "ERROR: Some character data was lost during transcoding in the dataset DB.XXXDAT. Either the data contains characters that are not representable in the new encoding or truncation occurred during transcoding." use the following code in program:

proc options option=config; run;
proc options group=languagecontrol; run;

/* Show the encoding value for the problematic data set */
%let dsn=db.xxxdat;
%let dsid=%sysfunc(open(&dsn,i));
%put &dsn ENCODING is: %sysfunc(attrc(&dsid,encoding));

/*Renaming item desc file  (encoding=any) allowed reading */
data temp;
set db.xxxdat (encoding=any);
run;