About Vish33

Vish33 · ‎05-07-2018

this looks like created cross-tabulation report from existing data. first of all it's not structured data to transpose here and it would need huge effort if you want to code this and import data from this Excel and transpose as you need. Try to find the source of this cross tab report that would be more helpful to you, if not found then you have to manually structure the data (at least column level) from excel then Import data in SAS.

Vish33 · ‎05-07-2018

Hi, I am unable to download your file seems vey big, please post the input sample data with 10 lines of input data and how you need the output.. that helps other users as well to understand your requirement.

Vish33 · ‎05-07-2018

you have missed '.' to represent the macro variable to correctly. use the below code. %macro one(pc); proc means noprint data= temp; var count &pc._error; output out= &pc.1 sum(count &pc._error) = ; run; %mend; %one(income);

Vish33 · ‎05-07-2018

Hi, I don't think its a bad idea to transpose as you want , but keeping In mind that you are deriving the required data in sql step, it would need additional step to get the required format i.e. transpose . here you can eliminate data step by using options. proc transpose data=two out=three(rename=(_name_=param col1=aval) drop= col2 col3 col4); by id trt; var numberofrecords durationinweeks compliance; run; Thanks, Vish

Vish33 · ‎05-04-2018

Try this.: data _null_; call symput('dt',put(intnx('day',today(),0),ddmmyy10.)); call symput('dtp6mon',put(intnx('month',today(),-6),ddmmyy10.)); run; %put dt=&dt.; %put dtp6mon=&dtp6mon.; proc sql; select Encounter_Num,ID,count(ER) as ER_Cnt from tbl_name group by Encounter_Num,ID having ER='Y' and date between "&dtp6mon."d and "&dt."d; quit;

Vish33 · ‎05-04-2018

sort the date field "mes" in ascending order before transpose . this should give dates in sequence.

Vish33 · ‎05-04-2018

you can create table with history data automatically using macro vars, but I am not sure if this is what you expect. below program will get you the past 4 years data automatically if you mention the source data and date field i.e required to check. data _null_; call symput('dt',put(intnx('day',today(),0),mmyy7.)); call symput('dtb4y',put(intnx('year',today(),-4),mmyy7.)); run; %put dt=&dt.; %put dtb4y=&dtb4y.; data want; set have; where actual_date between "&dtb4y."d and "&dt."d; run;

Vish33 · ‎05-04-2018

yes, we can transpose the tabulate output to get the sas dataset. proc tabulate data=WORK.Add_columns out= want; class mes name; tables name='',mes='' * n='' /misstext='0'; run; proc transpose data=want out=want1(drop=_name_); var n; id mes; by name; run;

Vish33 · ‎05-04-2018

Transpose wouldn't work as you have multiple same ID columns and would need unique value for ID column. But this can be done with cross-tabulation report with proc tabulate. proc tabulate data=WORK.Add_columns; class mes name; tables name='',mes='' * n='' /misstext='0'; run; Thanks, Vish

Vish33 · ‎05-03-2018

Hi, With the real data, in SAS DI you can always use SCD Type1 cluster to address this scenario. In SCD Type1: the new incoming data will update the existing data if there are any changes and keeps the original if no changes in transactions. Thanks, Vishnu

Vish33 · ‎05-03-2018

Try this: axis1 minor=none label=('') value=none ; axis2 minor=none label=(''); axis3 label=('') minor=none value=(angle=65) ; legend1 label=none shape=bar(3,3) cborder=white cblock=white origin=(24,); pattern1 color='#90EE90' ; pattern2 color=blue; title "3D Chart"; proc gchart data=have; vbar3d app / discrete sumvar=count group=fruit subgroup=App space=0 maxis=axis1 raxis=axis2 gaxis=axis3 outside=sum legend=legend1 shape=block autoref cframe=white ascending ; run; quit;

Vish33 · ‎05-02-2018

Hi, Can you post the code which you are using . Thanks, Vishnu

Vish33 · ‎05-02-2018

You are welcome! code always should work across versions. Is importing through wizard is failing? in that case we have to see the excel versions its supporting through wizard.

Vish33 · ‎05-02-2018

have you tried with XLS or XLSX Replace options.

Vish33 · ‎05-02-2018

Hi Kelly, Can you please post the code that you used for importing excel file.

Online Status	Offline
Date Last Visited	‎11-27-2019 12:34 PM

Re: Merging two datasets with alternating variables

Re: Merging two datasets with alternating variables

Re: appending .csv files onto a dataset daily

Re: Make macro variable for values of a variable

Re: How to combine two proc sql table and then export them as ods html...

Re: proc report summarize

Re: Dynamic Parameters

Re: Dynamic Parameters

Re: How to add quarters in SAS?

Re: Proc Means and output statement

Re: export dataset rows into multiples files .txt

Re: Make macro variable for values of a variable

Re: SAS Output help

Re: SAS Output help

Re: How to add quarters in SAS?

Re: How to combine multiple programs into one single program?

Re: Merging two datasets with alternating variables

Re: Help in my sql code

Re: Create new variables based on observations to statistics

Re: Create new variables based on observations to statistics

Re: Transpose crosstable into a hierarchical file

Re: Transpose crosstable into a hierarchical file

Re: Macro issue

Re: Help in my sql code

Re: SAS coding

Re: Create new variables based on observations to statistics

Re: Create new variables based on observations to statistics

Re: Create new variables based on observations to statistics

Re: Create new variables based on observations to statistics

Re: SAS DI Merging

Re: How to create 3d clustered column chart in sas

Re: How to create 3d clustered column chart in sas

Re: Cannot import excel to SAS

Re: Cannot import excel to SAS

Re: Cannot import excel to SAS

SAS Global Forum 2016