statgeek

/*
One common issue with using SQL pass through is that you sometimes want to use data that is on your SAS server. Rather than pass this information to the server, to be used in a subquery or join, you can pass the values directly by dynamically generating the code and lookup lists
*/

%let age = 14;

data test;
set sashelp.class end = eof;;
where age = &age.;

statgeek

data char_num;
char = "8.4"; output;
char = "10.5"; output;
run;

data char_date;
char = "2012-01-01";output;
char = "2014-02-08"; output;
run;

statgeek

/*This illustrates how to pipe a table and graph to ODS EXCEL
using macros. 

 1. Replace BY with WHERE statements 
 2. Add in ODS EXCEL options to name sheet 
 3. Wrap code in macro - note main ods excel statements are outside of the macro.  
 4. Create a list of origins to run this for 
 5. Call macro for each origin 

*/

statgeek

/*This is an example of exporting data to excel, with each section going to a 
separate sheet and each sheet taking the name of the group*/

*sorts your data to use BY logic;
proc sort data=sashelp.cars out=cars;
by origin;
run;

*removes proc title and by line which are usually printed by default;
ods  noptitle;

statgeek

/* 
Author: Jeff Myers
Source: https://communities.sas.com/t5/Graphics-Programming/Sankey-Diagram-Decision-Tree-etc/m-p/719812#
*/


data random;
    call streaminit(123);
    do id = 1 to 100;
        do cycle=1 to 5;

statgeek

/*This is an example of how to control which output goes to which file*/

ods html (ID="dest2") file='/home/fkhurshed/Demo1/testODS1.html';
ods html (ID="dest3") file='/home/fkhurshed/Demo1/testODS2.html';

ods html (ID="dest2") select BasicMeasures;
ods html (ID="dest3") select Quantiles;
proc univariate data=sashelp.class;
run;

statgeek

*An example/demo of how to convert a macro variable list that is space delimited to comma delimited.

%Let weightvar = WEIGHT HEIGHT AGE;

*uses the translate function to replace spaces with commas. If VALIDVARNAME=ANY this may not work;
%macro addCommas(varList=);
     %sysfunc(translate(&varList, %str(, ), %str( )))
%mend addCommas;

*example recoding;

statgeek

%macro drop_missing_pct(input_dsn = , output_dsn=, pct = , id_vars=);

*input_dsn = input data set name;
*output_dsn = output data set name;
*pct = missing percent, variables with a percentage of missing above this value are dropped;
*id_vars = space delimited list of variables that you do not want to include in the analysis such as ID variables;

statgeek

*https://communities.sas.com/t5/SAS-Programming/combining-multiple-columns/m-p/705769

/*This example illustrates how to combine variables from labels or variable names 
from a list of indicator variables

Author: F. Khurshed
Date: 2020-12-14
*/

data have;

statgeek

/*Modified from this question on SO: https://stackoverflow.com/questions/65219061/sas-loop-create-columns-from-the-records-which-are-having-a-value
Initial answer from Richard D.
*/

*sample data;
data have;
input id found  code $;
datalines;            
1   1      001
2   0      v58